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 19c, Version 19.1, for JSON Developer’s Guide
The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database Release 19c, Version 19.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.
Changes in Oracle Database Release 19c, Version 19.1, for JSON Developer’s Guide
The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database Release 19c, Version 19.1, are described.
- New Features
The following features are new in this release.
New Features
The following features are new in this release.
- JSON Materialized View Support
Performance enhancement: If you create a refresh-on-statement materialized view overjson_table
and some other conditions apply then a query that matches the query defining the view can be rewritten to a materialized-view table access. You can use this feature instead of creating multiple functional indexes. - SQL Function JSON_MERGEPATCH: Declarative Update of JSON Documents
You can now update a JSON document declaratively, using new SQL functionjson_mergepatch
. You can apply one or more changes to multiple documents using a single statement. This feature improves the flexibility of JSON update operations. - New SQL/JSON Function JSON_SERIALIZE and JSON Data Guide Support for GeoJSON Data
You can use new SQL functionjson_serialize
to serialize JSON data to text or to UTF-encodedBLOB
data. SQL aggregate functionjson_dataguide
can now detect GeoJSON geographic data in your documents. You can use it to create a view that projects such data as SQL data typeSDO_GEOMETRY
. - Syntax Simplifications
Syntax simplifications are offered for SQL/JSON path expressions and SQL/JSON generation with functionjson_object
. A new SQL query clause,NESTED
, provides a simple alternative to usingjson_table
withLEFT OUTER JOIN
. - Mapping of JSON Data To and From SQL Object Types
This feature enables the mapping of JSON data to and from user-defined SQL object types and collections. You can convert JSON data to an instance of a SQL object type using SQL/JSON functionjson_value
. In the opposite direction, you can generate JSON data from an instance of a SQL object type using SQL/JSON functionjson_object
orjson_array
.
JSON Materialized View Support
Performance enhancement: If you create a refresh-on-statement materialized view over json_table
and some other conditions apply then a query that matches the query defining the view can be rewritten to a materialized-view table access. You can use this feature instead of creating multiple functional indexes.
Parent topic: New Features
SQL Function JSON_MERGEPATCH: Declarative Update of JSON Documents
You can now update a JSON document declaratively, using new SQL function
json_mergepatch
. You can apply one or more changes to multiple
documents using a single statement. This feature improves the flexibility of JSON update
operations.
Related Topics
Parent topic: New Features
New SQL/JSON Function JSON_SERIALIZE and JSON Data Guide Support for GeoJSON Data
You can use new SQL function json_serialize
to serialize JSON data to text or to UTF-encoded BLOB
data. SQL aggregate function json_dataguide
can now detect GeoJSON geographic data in your documents. You can use it to create a view that projects such data as SQL data type SDO_GEOMETRY
.
Related Topics
Parent topic: New Features
Syntax Simplifications
Syntax simplifications are offered for SQL/JSON path expressions and
SQL/JSON generation with function json_object
. A new SQL query clause,
NESTED
, provides a simple alternative to using
json_table
with LEFT OUTER JOIN
.
Mapping of JSON Data To and From SQL Object Types
This feature enables the mapping of JSON data to and from user-defined SQL object types and collections. You can convert JSON data to an instance of a SQL object type using SQL/JSON function json_value
. In the opposite direction, you can generate JSON data from an instance of a SQL object type using SQL/JSON function json_object
or json_array
.
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:
Parent topic: New Features
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.
Parent topic: New Features
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
Parent topic: New Features
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.