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

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

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

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

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 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).

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.

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.

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.

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

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.

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.

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.