Skip to main content
Oracle Help Center

Sign Out

Sign In

  • Table of Contents
  • Search
  • Print
  • Download
  1. Database
  2. Oracle
  3. Oracle Database
  4. Release 19

JSON Developer's Guide

  • Table of Contents
  • Search
  • Print
  • Download

Table of Contents

Expand AllCollapse All
  • List of Examples
  • List of Figures
  • 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 Release 19c, Version 19.1, for JSON Developer’s Guide
      • New Features
        • JSON Materialized View Support
        • SQL Function JSON_MERGEPATCH: Declarative Update of JSON Documents
        • New SQL/JSON Function JSON_SERIALIZE and JSON Data Guide Support for GeoJSON Data
        • Syntax Simplifications
        • Mapping of JSON Data To and From SQL Object Types
    • Changes in Oracle Database Release 18c, Version 18.1, for JSON Developer’s Guide
      • New Features
        • SQL Enhancements
        • Sharding Support
        • Performance Improvements for LOB Storage
        • JSON Search Index Support for Longer Field Names
      • Deprecated Features
  • 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
    • 11 Updating a JSON Document with JSON Merge Patch
  • Part IV Query JSON Data
    • 12 Simple Dot-Notation Access to JSON Data
    • 13 SQL/JSON Path Expressions
      • 13.1 Overview of SQL/JSON Path Expressions
      • 13.2 SQL/JSON Path Expression Syntax
        • 13.2.1 Basic SQL/JSON Path Expression Syntax
        • 13.2.2 SQL/JSON Path Expression Syntax Relaxation
      • 13.3 SQL/JSON Path Expression Item Methods
      • 13.4 ISO 8601 Date and Time Support
      • 13.5 Types in Comparisons
    • 14 Clauses Used in SQL Query Functions and Conditions
      • 14.1 RETURNING Clause for SQL Query Functions
      • 14.2 Wrapper Clause for SQL/JSON Query Functions JSON_QUERY and JSON_TABLE
      • 14.3 Error Clause for SQL Query Functions and Conditions
      • 14.4 Empty-Field Clause for SQL/JSON Query Functions
      • 14.5 ON MISMATCH Clause for JSON_VALUE
    • 15 SQL/JSON Condition JSON_EXISTS
      • 15.1 Using Filters with JSON_EXISTS
      • 15.2 JSON_EXISTS as JSON_TABLE
    • 16 SQL/JSON Function JSON_VALUE
      • 16.1 Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value
      • 16.2 SQL/JSON Function JSON_VALUE Applied to a null JSON Value
      • 16.3 Using JSON_VALUE To Instantiate a User-Defined Object Type Instance
      • 16.4 JSON_VALUE as JSON_TABLE
    • 17 SQL/JSON Function JSON_QUERY
      • 17.1 JSON_QUERY as JSON_TABLE
    • 18 SQL/JSON Function JSON_TABLE
      • 18.1 SQL NESTED Clause Instead of JSON_TABLE
      • 18.2 COLUMNS Clause of SQL/JSON Function JSON_TABLE
      • 18.3 JSON_TABLE Generalizes SQL/JSON Query Functions and Conditions
      • 18.4 Using JSON_TABLE with JSON Arrays
      • 18.5 Creating a View Over JSON Data Using JSON_TABLE
    • 19 Oracle SQL Function JSON_SERIALIZE
    • 20 JSON Data Guide
      • 20.1 Overview of JSON Data Guide
      • 20.2 Persistent Data-Guide Information: Part of a JSON Search Index
      • 20.3 Data-Guide Formats and Ways of Creating a Data Guide
      • 20.4 JSON Data-Guide Fields
      • 20.5 Data-Dictionary Views For Persistent Data-Guide Information
      • 20.6 Specifying a Preferred Name for a Field Column
      • 20.7 Creating a View Over JSON Data Based on Data-Guide Information
        • 20.7.1 Creating a View Over JSON Data Based on a Hierarchical Data Guide
        • 20.7.2 Creating a View Over JSON Data Based on a Path Expression
      • 20.8 Adding and Dropping Virtual Columns For JSON Fields Based on Data-Guide Information
        • 20.8.1 Adding Virtual Columns For JSON Fields Based on a Hierarchical Data Guide
        • 20.8.2 Adding Virtual Columns For JSON Fields Based on a Data Guide-Enabled Search Index
        • 20.8.3 Dropping Virtual Columns for JSON Fields Based on Data-Guide Information
      • 20.9 Change Triggers For Data Guide-Enabled Search Index
        • 20.9.1 User-Defined Data-Guide Change Triggers
      • 20.10 Multiple Data Guides Per Document Set
      • 20.11 Querying a Data Guide
      • 20.12 A Flat Data Guide For Purchase-Order Documents
      • 20.13 A Hierarchical Data Guide For Purchase-Order Documents
  • Part V Generation of JSON Data
    • 21 Generation of JSON Data with SQL/JSON Functions
      • 21.1 Overview of SQL/JSON Generation Functions
      • 21.2 Handling of Input Values For SQL/JSON Generation Functions
      • 21.3 SQL/JSON Function JSON_OBJECT
      • 21.4 SQL/JSON Function JSON_ARRAY
      • 21.5 SQL/JSON Function JSON_OBJECTAGG
      • 21.6 SQL/JSON Function JSON_ARRAYAGG
  • Part VI PL/SQL Object Types for JSON
    • 22 Overview of PL/SQL Object Types for JSON
    • 23 Using PL/SQL Object Types for JSON
  • Part VII GeoJSON Geographic Data
    • 24 Using GeoJSON Geographic Data
  • Part VIII Performance Tuning for JSON
    • 25 Overview of Performance Tuning for JSON
    • 26 Indexes for JSON Data
      • 26.1 Overview of Indexing JSON Data
      • 26.2 How To Tell Whether a Function-Based Index for JSON Data Is Picked Up
      • 26.3 Creating Bitmap Indexes for SQL/JSON Condition JSON_EXISTS
      • 26.4 Creating JSON_VALUE Function-Based Indexes
      • 26.5 Using a JSON_VALUE Function-Based Index with JSON_TABLE Queries
      • 26.6 Using a JSON_VALUE Function-Based Index with JSON_EXISTS Queries
      • 26.7 Data Type Considerations for JSON_VALUE Indexing and Querying
      • 26.8 Indexing Multiple JSON Fields Using a Composite B-Tree Index
      • 26.9 JSON Search Index: Ad Hoc Queries and Full-Text Search
    • 27 In-Memory JSON Data
      • 27.1 Overview of In-Memory JSON Data
      • 27.2 Populating JSON Data Into the In-Memory Column Store
      • 27.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

Search

Print

Download

PDF for offline and print

  • Previous
  • Next
  1. JSON Developer's Guide
  2. Generation of JSON Data

Part V Generation of JSON Data

You can use SQL to generate JSON data from other kinds of database data programmatically, using SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg.

  • Generation of JSON Data with SQL/JSON Functions
    SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg are presented.
  • Previous
  • Next
Back to main content
  • About Oracle
  • Contact Us
  • Terms of Use and Privacy
  • Cookie Preferences
Copyright © 2015, 2019, Oracle and/or its affiliates.
  • Previous
  • Next
Video