Oracle Database JSON Developer's Guide is a new book in Oracle Database 12c Release 2 (18.104.22.168).
Information about using JSON data in Oracle Database 12c Release 1 (22.214.171.124) is available in Oracle XML DB Developer’s Guide.
Changes in Oracle Database 12c Release 2 (126.96.36.199) for JSON Developer’s Guide
The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database 12c Release 2 (188.8.131.52) are described.
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.
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.
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.
SQL/JSON Functions and Conditions Added to PL/SQL
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
TIMESTAMP WITH TIME ZONE with SQL/JSON functions
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
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.
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_query (any combination) to fewer invocations of
json_table. This typically improves performance because the data is parsed only once for each
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
json_table projects specific JSON data as
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
PL/SQL APIs For JSON Data
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),
BLOB in a sharded table. You cannot query JSON data across multiple shards unless it is stored as