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

Oracle Database JSON Developer's Guide is a new book in Oracle Database 12c Release 2 (

Information about using JSON data in Oracle Database 12c Release 1 ( is available in Oracle XML DB Developer’s Guide.


Changes in Oracle Database 12c Release 2 ( for JSON Developer’s Guide

The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database 12c Release 2 ( are described.


New Features

The following features are new in this release.


Storage and Management of JSON Data


JSON Data Partitioning

You can now partition a table using a JSON virtual column as the partitioning key.

JSON Search Index on a Partitioned Table

You can now create a JSON search index on a partitioned base table (with range, list, hash, or interval partitioning).

Queries of JSON Data


Path Expression Enhancements

JSON path expressions can now include filter expressions that must be satisfied by the matching data and transformation methods that can transform it.

Simple Dot-Notation Syntax Supports Array Access

You can now access arrays and their elements using the simple dot-notation syntax.

Data Guide

You can now create a JSON data guide, which captures the structural information of a set of JSON documents. It acts as a derived schema and is maintained along with the JSON data that it represents. It can also record statistics about scalar values used in the documents.

See Also:

JSON Data Guide

SQL/JSON Functions and Conditions Added to PL/SQL

SQL/JSON functions json_value, json_query, json_object, and json_array, as well as SQL/JSON condition json_exists, have been added to the PL/SQL language as built-in functions (json_exists is a Boolean function in PL/SQL).

JSON_VALUE and JSON_TABLE Support for Additional Data Types

You can now use SQL data types SDO_GEOMETRY, DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE with SQL/JSON functions json_value and json_table.

You can specify any of these as the return data type for SQL/JSON function json_value, and you can specify any of them as a column data type for SQL/JSON function json_table.

SDO_GEOMETRY is used for Oracle Spatial and Graph data. In particular, this means that you can use these functions with GeoJSON data, which is a format for encoding geographic data in JSON.



Search Enhancements

You can use a simpler syntax to create a JSON search index. Range search is now available for numbers and JSON strings that can be cast as built-in date and time types.

SQL/JSON Query Functions and Conditions Rewritten to JSON_TABLE

The optimizer will now often rewrite multiple invocations of json_exists, json_value, and json_query (any combination) to fewer invocations of json_table. This typically improves performance because the data is parsed only once for each json_table invocation.

JSON Columns In the In-Memory Column Store

You can now store JSON columns in the in-memory column store, to improve query performance.

Materialized Views Over JSON Data

You can now create a materialized view over JSON data that is projected as VARCHAR2 or NUMBER columns.

SQL/JSON function json_table projects specific JSON data as VARCHAR2 or NUMBER columns. You can typically increase query performance by creating a materialized view over such columns. The view must be read-only: a FOR UPDATE clause is not allowed when creating it. Both full and incremental view refresh are supported. You can often increase query performance further by creating indexes on the view columns.



SQL/JSON Functions for Generating JSON Data

You can now construct JSON data programmatically using SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg.


PL/SQL APIs are now available to provide (1) data guide operations and (2) get and set operations on JSON object types that are backed by an in-memory, hierarchical, programmatic representation.

JSON Columns in a Sharded Table

You can now create a JSON column in a sharded table and query that JSON data.

You can store JSON data in a column of type VARCHAR2 (up to 32,767 bytes), CLOB, or BLOB in a sharded table. You cannot query JSON data across multiple shards unless it is stored as VARCHAR2.