1/42

Contents

List of Examples

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions
• Code Examples
  • Pretty Printing of JSON Data
  • Execution Plans
  • Reminder About Case Sensitivity

Changes in This Release for Oracle Database JSON Developer's Guide

• Changes in Oracle Database 12c Release 2 (12.2.0.1) for Oracle Database
  • New Features
    • Storage and Management of JSON Data
      • JSON Data Partitioning
      • JSON Search Index on a Partitioned Table
    • Queries of JSON Data
      • Path Expression Enhancements
      • Simple Dot-Notation Syntax Supports Array Access
      • Data Guide
      • SQL/JSON Functions and Conditions Added to PL/SQL
      • JSON_VALUE and JSON_TABLE Support for Additional Data Types
    • Performance
      • Search Enhancements
      • SQL/JSON Query Functions and Conditions Rewritten to JSON_TABLE
      • JSON Columns In the In-Memory Column Store
      • Materialized Views Over JSON Data
    • Other
      • SQL/JSON Functions for Generating JSON Data
      • PL/SQL APIs For JSON Data
      • JSON Columns in a Sharded Table

Part I Introduction to JSON Data and Oracle Database

1 JSON in Oracle Database

• 1.1 Overview of JSON in Oracle Database
• 1.2 Getting Started Using JSON with Oracle Database
• 1.3 Oracle Database Support for JSON

2 JSON Data

• 2.1 Overview of JSON
• 2.2 JSON Syntax and the Data It Represents
• 2.3 JSON Compared with XML

Part II Store and Manage JSON Data

3 Overview of Storing and Managing JSON Data

4 Creating a Table With a JSON Column

• 4.1 Determining Whether a Column Necessarily Contains JSON Data

5 SQL/JSON Conditions IS JSON and IS NOT JSON

• 5.1 Unique Versus Duplicate Fields in JSON Objects
• 5.2 About Strict and Lax JSON Syntax
• 5.3 Specifying Strict or Lax JSON Syntax

6 Character Sets and Character Encoding for JSON Data

7 Partitioning JSON Data

8 Replication of JSON Data

Part III Insert, Update, and Load JSON Data

9 Overview of Inserting, Updating, and Loading JSON Data

10 Loading External JSON Data

Part IV Query JSON Data

11 Simple Dot-Notation Access to JSON Data

12 SQL/JSON Path Expressions

• 12.1 Overview of SQL/JSON Path Expressions
• 12.2 SQL/JSON Path Expression Syntax
  • 12.2.1 Basic SQL/JSON Path Expression Syntax
  • 12.2.2 SQL/JSON Path Expression Syntax Relaxation

13 Clauses Used in SQL/JSON Query Functions and Conditions

• 13.1 RETURNING Clause for SQL/JSON Query Functions
• 13.2 Wrapper Clause for SQL/JSON Query Functions JSON_QUERY and JSON_TABLE
• 13.3 Error Clause for SQL/JSON Query Functions and Conditions
• 13.4 Empty-Field Clause for SQL/JSON Query Functions

14 SQL/JSON Condition JSON_EXISTS

• 14.1 Using Filters with JSON_EXISTS
• 14.2 JSON_EXISTS as JSON_TABLE

15 SQL/JSON Function JSON_VALUE

• 15.1 Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value
• 15.2 SQL/JSON Function JSON_VALUE Applied to a null JSON Value
• 15.3 JSON_VALUE as JSON_TABLE

16 SQL/JSON Function JSON_QUERY

• 16.1 JSON_QUERY as JSON_TABLE

17 SQL/JSON Function JSON_TABLE

• 17.1 JSON_TABLE Generalizes SQL/JSON Query Functions and Conditions
• 17.2 Using JSON_TABLE with JSON Arrays
• 17.3 Creating a View Over JSON Data Using JSON_TABLE

18 JSON Data Guide

• 18.1 Overview of JSON Data Guide
• 18.2 Persistent Data-Guide Information: Part of a JSON Search Index
• 18.3 Data-Guide Formats and Ways of Creating a Data Guide
• 18.4 JSON Data-Guide Fields
• 18.5 Specifying a Preferred Name for a Field Column
• 18.6 Creating a View Over JSON Data Based on Data-Guide Information
  • 18.6.1 Creating a View Over JSON Data Based on a Hierarchical Data Guide
  • 18.6.2 Creating a View Over JSON Data Based on a Path Expression
• 18.7 Adding and Dropping Virtual Columns For JSON Fields Based on Data-Guide Information
  • 18.7.1 Adding Virtual Columns For JSON Fields Based on a Hierarchical Data Guide
  • 18.7.2 Adding Virtual Columns For JSON Fields Based on a Data Guide-Enabled Search Index
  • 18.7.3 Dropping Virtual Columns for JSON Fields Based on Data-Guide Information
• 18.8 Change Triggers For Data Guide-Enabled Search Index
  • 18.8.1 User-Defined Data-Guide Change Triggers
• 18.9 Multiple Data Guides Per Document Set
• 18.10 Querying a Data Guide
• 18.11 A Flat Data Guide For Purchase-Order Documents
• 18.12 A Hierarchical Data Guide For Purchase-Order Documents

Part V Generation of JSON Data

19 Generation of JSON Data With SQL/JSON Functions

• 19.1 Overview of SQL/JSON Generation Functions
• 19.2 JSON_OBJECT SQL/JSON Function
• 19.3 JSON_ARRAY SQL/JSON Function
• 19.4 JSON_OBJECTAGG SQL/JSON Function
• 19.5 JSON_ARRAYAGG SQL/JSON Function

Part VI PL/SQL Object Types for JSON

20 Overview of PL/SQL Object Types for JSON

21 Using PL/SQL Object Types for JSON

Part VII GeoJSON Geographic Data

22 Using GeoJSON Geographic Data

Part VIII Performance Tuning for JSON

23 Overview of Performance Tuning for JSON

24 Indexes for JSON Data

• 24.1 Overview of Indexing JSON Data
• 24.2 How To Tell Whether a Function-Based Index for JSON Data Is Picked Up
• 24.3 Creating Bitmap Indexes for SQL/JSON Condition JSON_EXISTS
• 24.4 Creating JSON_VALUE Function-Based Indexes
• 24.5 Using a JSON_VALUE Function-Based Index with JSON_TABLE Queries
• 24.6 Using a JSON_VALUE Function-Based Index with JSON_EXISTS Queries
• 24.7 Data Type Considerations for JSON_VALUE Indexing and Querying
• 24.8 Indexing Multiple JSON Fields Using a Composite B-Tree Index
• 24.9 JSON Search Index: Ad Hoc Queries and Full-Text Search

25 In-Memory JSON Data

• 25.1 Overview of In-Memory JSON Data
• 25.2 Populating JSON Data Into the In-Memory Column Store
• 25.3 Upgrading Tables With JSON Data For Use With the In-Memory Column Store

A Oracle Database JSON Restrictions

B Diagrams for Basic SQL/JSON Path Expression Syntax

Index