Changes in This Release for Oracle Database JSON Developer's Guide
Changes in Oracle Database for this book are described.
Oracle Database JSON Developer's Guide was a new book in Oracle Database 12c Release 2 (12.2.0.1). Information about using JSON data in Oracle Database 12c Release 1 (12.1.0.2) is available in Oracle XML DB Developer’s Guide.
- Changes in Oracle Database Release 18c, Version 18.1, for JSON Developer’s Guide
The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database Release 18c, Version 18.1, are described. - Changes in Oracle Database 12c Release 2 (12.2.0.1) for JSON Developer’s Guide
The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database 12c Release 2 (12.2.0.1) are described.
Changes in Oracle Database Release 18c, Version 18.1, for JSON Developer’s Guide
The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database Release 18c, Version 18.1, are described.
- New Features
The following features are new in this release. - Deprecated Features
The following features are deprecated in this release, and may be desupported in a future release.
New Features
The following features are new in this release.
- SQL Enhancements
Multiple SQL enhancements are provided for storing and querying JSON data in the database. - Sharding Support
Sharding support for JSON data has been enhanced. - Performance Improvements for LOB Storage
Performance has been improved for JSON data that uses LOB storage. This particularly affects the common use case of reading and writing an entire JSON document stored as a LOB. - JSON Search Index Support for Longer Field Names
The maximum JSON field name length supported for a JSON search index is now 255 bytes (formerly it was 64 bytes).
SQL Enhancements
Multiple SQL enhancements are provided for storing and querying JSON data in the database.
You Can Specify That a SQL Expression Returns JSON Data
You can apply SQL function treat
, with keywords AS JSON
, to a SQL expression to specify that the expression returns JSON data. This is useful in situations where Oracle cannot determine that the result is JSON data. It is also useful in situations where you want to force some text to be interpreted as JSON data. For example, you can use it to tell Oracle to interpret a VARCHAR2
value of {}
not as a string but as an empty JSON object.
See Also:
LOB Results for JSON_VALUE, JSON_QUERY, and JSON_TABLE
SQL/JSON function json_value
can now return a CLOB
instance.
SQL/JSON function json_query
can now return a CLOB
or BLOB
instance. A BLOB
result is in the AL32UTF8 character set.
As before, the data type of a json_table
column depends on whether the column is specified as FORMAT JSON
. If it is, the json_query
return types are supported; if it is not, the json_value
return types are supported.
Previously:
-
as return types.json_value
supported onlyVARCHAR2
,NUMBER
,DATE
,TIMESTAMP
,TIMESTAMP WITH TIME ZONE
, andSDO_GEOMETRY
-
json_query
supported onlyVARCHAR2
as return type.
See Also:
SQL/JSON Generation Function Enhancements
-
Optional keyword
STRICT
has been added. When present, any inputs that are declared or otherwise expected to be JSON data are checked to ensure that they are, in fact, well-formed JSON data. If any of them is not then an error is raised.(You can declare input to be JSON by using keywords
FORMAT JSON
or by using SQL functiontreat
with keywordsAS JSON
. Input is expected to be JSON data if it is from a table column that has anis json
constraint or it is the output of another JSON generation function.) -
The SQL/JSON generation functions (
json_object
,json_array
,json_objectagg
, andjson_arrayagg
) can now return aCLOB
orBLOB
instance. ABLOB
result is in the AL32UTF8 character set. -
The input expression to a generation function can now be of any of these SQL data types (in addition to
NUMBER
,VARCHAR2
,DATE
andTIMESTAMP
):-
BINARY_DOUBLE
-
BINARY_FLOAT
-
CLOB
-
BLOB
-
NVARCHAR2
-
RAW
-
TIMESTAMP WITH TIME ZONE
-
TIMESTAMP WITH LOCAL TIME ZONE
-
INTERVAL YEAR TO MONTH
-
INTERVAL DAY TO SECOND
How the result of generation is rendered in JSON depends on the data type of the input.
-
Item Methods for SQL/JSON Path Expressions
-
New item methods have been added:
numberOnly()
,stringOnly()
,boolean()
,booleanOnly()
,size()
, andtype()
.The data-type conversion methods with “only” in their name are the same as the corresponding methods with names without “only”, except that the former convert only JSON values that are of the given type (e.g.,
number
) to the related SQL data type (e.g.NUMBER
). The methods without “only” in the name allow conversion, when possible, of any JSON value to the given SQL data type. -
All item methods can now be used in path expressions for SQL/JSON functions
json_value
,json_query
, andjson_table
(columns). In Oracle Database 12c Release 2 (12.2.0.1), item methods could only be used in path expressions for SQL/JSON conditionjson_exists
. -
Item method
string()
can now returnCLOB
orBLOB
(in addition toVARCHAR2
). The default isVARCHAR2(4000)
.
See Also:
JSON Data Guide Enhancements
-
Oracle SQL aggregate function
json_dataguide
now accepts optional formatting arguments:-
You can return a hierarchical dataguide, by specifying keyword
FORMAT
with argumentDBMS_JSON.FORMAT_HIERARCHICAL
. -
You can specify pretty-printing (indentation to improve readability) of the returned dataguide, by specifying keyword
PRETTY
with argumentDBMS_JSON.PRETTY
.
-
-
New data-dictionary views are available, to show you the dataguide information recorded for individual JSON object fields in a dataguide-enabled search index:
USER_JSON_DATAGUIDE_FIELDS
,ALL_JSON_DATAGUIDE_FIELDS
,DBA_JSON_DATAGUIDE_FIELDS
. (These are in addition to viewsUSER_JSON_DATAGUIDES
,ALL_JSON_DATAGUIDES
, andDBA_JSON_DATAGUIDES
, which list the tables that have JSON columns with a dataguide-enabled search index.) -
For JSON documents that contain an array of scalar values, a dataguide now records the scalar types as well as the type
ARRAY
. The recorded path for the scalar values is indicated in a flat data guide by appending[*]
to the path recorded for the array itself.
See Also:
Data-Dictionary Views That Record the Presence of JSON Columns
Data-dictionary views USER_JSON_COLUMNS
, ALL_JSON_COLUMNS
, and DBA_JSON_COLUMNS
now list the views, as well as the tables, that have columns with JSON data.
See Also:
Oracle Database Reference for information about ALL_JSON_COLUMNS
and the related data-dictionary views
SQL/JSON Function JSON_TABLE Syntax
The syntax of json_table
has been enhanced by making it simpler for some common use cases:
-
You can now use simple dot-notation syntax in place of a path expression.
-
If a column is the projection of a JSON object field, and if you want the column to have the same name as the field, then you need not provide a path expression to that object — the path is inferred from the column name.
See Also:
ON STATEMENT Support For JSON_TABLE Materialized Views
You can now use keywords ON STATEMENT
when creating a materialized view using a json_table
query. Using ON STATEMENT
instead of ON COMMIT
means that the view is automatically synchronized for each DML statement against the base table.
New SQL Function TO_UTC_TIMESTAMP_TZ
SQL function to_UTC_timestamp_tz
takes as input an ISO 8601 date format string and returns an instance of SQL data type TIMESTAMP WITH TIMEZONE
. It normalizes the input to UTC time (Coordinated Universal Time, formerly Greenwich Mean Time). Unlike SQL function to_timestamp_tz
, the new function assumes that the input string uses the ISO 8601 date format, defaulting the time zone to UTC 0.
A typical use of this function would be to provide its output to SQL function sys_extract_UTC
, obtaining a UTC time that is then passed as a SQL bind variable to SQL/JSON condition json_exists
, to perform a time-stamp range comparison.
See Also:
ISO 8601 for information about the ISO date formats
New Oracle SQL Condition JSON_EQUAL
Oracle SQL condition json_equal
compares two JSON values and returns true if they are equal, false otherwise. For this comparison, insignificant whitespace and insignificant object member order are ignored. For example, JSON objects are equal if they have the same members, regardless of their order. However, if either of two compared objects has one or more duplicate fields then the value returned by json_equal
is unspecified.
Sharding Support
Sharding support for JSON data has been enhanced.
The following are now supported:
-
JSON search index on sharded tables, whether user-managed or system-managed.
-
Cross-shard (and in-shard) queries for JSON data returned as
CLOB
orBLOB
from SQL and PL/SQL. This includes projection of JSON data toCLOB
andBLOB
columns using SQL/JSON functionjson_table
. -
Evaluation on individual shards of cross-shard queries that use SQL/JSON functions and conditions. This includes the use of the following:
-
Function
json_value
and aggregate functionjson_dataguide
in aSELECT
clause -
Function
json_table
in aFROM
clause -
Function
json_value
, and conditionsjson_exists
andjson_textcontains
, in aWHERE
clause.
-
-
Automatic relocation of base-table and index storage-table partitions during chunk migration.
See Also:
Performance Improvements for LOB Storage
Performance has been improved for JSON data that uses LOB storage. This particularly affects the common use case of reading and writing an entire JSON document stored as a LOB.
JSON Search Index Support for Longer Field Names
The maximum JSON field name length supported for a JSON search index is now 255 bytes (formerly it was 64 bytes).
Note:
You must rebuild any JSON search indexes and Oracle Text indexes created prior to Oracle Database 18c if they index JSON data that contains object fields with names longer than 64 bytes. See Oracle Database Upgrade Guide for more information.
Related Topics
Deprecated Features
The following features are deprecated in this release, and may be desupported in a future release.
-
SQL/JSON functions returning a Boolean JSON value as a number (zero or one). Return the value as
VARCHAR2
or (in PL/SQL only) asBOOLEAN
; do not return it asNUMBER
. If you really need a SQL numeric value then you can use SQLDECODE
orCASE WHEN
to obtain zero or one from aVARCHAR2
value.
Changes in Oracle Database 12c Release 2 (12.2.0.1) for JSON Developer’s Guide
The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database 12c Release 2 (12.2.0.1) are described.
- New Features
The following features are new in this release.
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. - SQL/JSON Functions and Conditions Added to PL/SQL
SQL/JSON functionsjson_value
,json_query
,json_object
, andjson_array
, as well as SQL/JSON conditionjson_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 typesSDO_GEOMETRY
,DATE
,TIMESTAMP
, andTIMESTAMP WITH TIME ZONE
with SQL/JSON functionsjson_value
andjson_table
.
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.
See Also:
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:
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).
See Also:
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.
Performance
- 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 ofjson_exists
,json_value
, andjson_query
(any combination) to fewer invocations ofjson_table
. This typically improves performance because the data is parsed only once for eachjson_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 asVARCHAR2
orNUMBER
columns.
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.
See Also:
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.
Other
- SQL/JSON Functions for Generating JSON Data
You can now construct JSON data programmatically using SQL/JSON functionsjson_object
,json_array
,json_objectagg
, andjson_arrayagg
. - PL/SQL APIs For JSON Data
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.
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
.
See Also:
PL/SQL APIs For JSON Data
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.
See Also:
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
.