Oracle Database JSON Developer's Guide is a new book in Oracle Database 12c Release 2 (188.8.131.52).
Information about using JSON data in Oracle Database 12c Release 1 (184.108.40.206) is available in Oracle XML DB Developer’s Guide.
The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database 12c Release 2 (220.127.116.11) are described.
The following features are new in this release.
You can now partition a table using a JSON virtual column as the partitioning key.
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.
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
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.
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
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
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.
You can now construct JSON data programmatically using SQL/JSON functions
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