JSON Support Enhancements in Oracle Database 18c

Oracle 18c includes several enhancements to the JSON functionality in the database. This article details some of them, as well as linking to separate articles on others.

LOB Return Types for JSON_VALUE, JSON_QUERY, and JSON_TABLE Functions
LOB Support for SQL/JSON Generation Functions
TREAT(... AS JSON)
JSON_EQUAL Condition
JSON Data Guide Enhancements
JSON_TABLE Enhancements
TO_UTC_TIMESTAMP_TZ
SODA for PL/SQL
Miscellaneous

LOB Return Types for JSON_VALUE, JSON_QUERY, and JSON_TABLE Functions

The JSON_VALUE function now supports the following return types, including the new CLOB return type

{ VARCHAR2 [ ( size [BYTE | CHAR] ) ]
| NUMBER [ ( precision [, scale] ) ]
| DATE
| TIMESTAMP
| TIMESTAMP WITH TIME ZONE
| CLOB
| SDO_GEOMETRY
}

The JSON_QUERY function now supports the following return types, including the new CLOB and BLOB return types.

{ VARCHAR2 [ ( size [BYTE | CHAR] ) ]
| CLOB
| BLOB
}

The return values of the JSON_TABLE function depends on the column definitions used. If a column is defined using the FORMAT JSON clause the JSON_QUERY return types are supported, otherwise the JSON_VALUE return types are supported.

You can see examples of using these functions here.

LOB Support for SQL/JSON Generation Functions

The SQL/JSON generation functions JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY and JSON_ARRAYAGG can now return LOB results, with the output determined by the RETURNING clause. The valid options are as follows.

{ VARCHAR2 [ ( size [BYTE | CHAR] ) ]
| CLOB
| BLOB
}

As with 12.2, if no RETURNING clause is specified or if RETURNING VARCHAR2 is specified is specified, the result is returned as a VARCHAR2(4000) type.

You can see examples of using these functions here.

Not surprisingly, the SQL/JSON generation functions have also been extended to accept CLOB and BLOB input parameters, as well as some additional types. The following data types are now supported as input expressions.

-- 12cR2
NUMBER
VARCHAR2
DATE
TIMESTAMP
-- 18c
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

The way these types are handled is described fully here, but in summary.

Strings Types : Special characters are escaped and the string is enclosed in double quotes.
Numeric Types : Not enclosed by quotes.
RAW and BLOB Types: Hexadecimal JSON string, enclosed in double quotes.
DATE, TIMESTAMP and INTERVAL Types : Converted to ISO 8601 format and enclosed in double quotes.
BOOLEAN Types : JSON true or false, not enclosed by quotes.
NULL : JSON null, not enclosed by quotes.
EMPTY_CLOB and EMPTY_BLOB : JSON empty string, empty double quotes.

TREAT(... AS JSON)

In Oracle 18c the TREAT function can be used in SQL to change the declared type of an expression to JSON, where the expression is an object containing JSON data. You can read more about this in the following article.

TREAT(... AS JSON) in Oracle Database 18c

JSON_EQUAL Condition

The JSON_EQUAL condition was introduced in 18c to allow JSON documents to be compared regardless of member order of document formatting. You can read more about this in the following article.

JSON_EQUAL Condition in Oracle Database 18c

JSON Data Guide Enhancements

Oracle 18c includes a number of enhancements to the JSON data guide functionality, which you can read more about this in the following article.

JSON Data Guide Enhancements in Oracle Database 18c

JSON_TABLE Enhancements

Oracle 18c includes simplified syntax for the JSON_TABLE function, and support for ON STATEMENT materialized views. You can read more about these enhancements in the following article.

JSON_TABLE Enhancements in Oracle Database 18c

TO_UTC_TIMESTAMP_TZ

Oracle 18c introduced the new TO_UTC_TIMESTAMP_TZ function to help deal with ISO 8601 date strings. You can read more about this in the following article.

TO_UTC_TIMESTAMP_TZ Function in Oracle Database 18c

SODA for PL/SQL

Oracle 18c introduced a PL/SQL API for interacting directly with SODA collections and documents. You can read more about this in the following article.

Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c

Miscellaneous

The {USER|ALL|DBA}_JSON_COLUMNS views now list views as well as tables that contain JSON columns.
There are a number of enhancements related to JSON data and sharding, as described here.
The documentation claims the performance of LOB storage for JSON data has improved when reading and writing an entire JSON document that is stored as a LOB.
JSON Search Indexes : The maximum JSON field name length has been extended from 64 bytes to 255 bytes. Upgraded databases will need their JSON search indexes rebuilt to take advantage of this.
The SQL/JSON generation functions can include an optional keyword STRICT, which checks inputs for well formed JSON. If the JSON is not well formed an error will be raised.
New SQL/JSON path expression item methods have been added, with the full list described here. All item methods are supported in path expressions for the JSON_VALUE, JSON_QUERY and JSON_TABLE SQL/JSON functions. Where appropriate these item methods support LOBs.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)