8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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
Related articles.
- TREAT(... AS JSON) in Oracle Database 18c
- JSON_EQUAL Condition in Oracle Database 18c
- JSON Data Guide Enhancements in Oracle Database 18c
- JSON_TABLE Enhancements in Oracle Database 18c
- Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
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
andBLOB
Types: Hexadecimal JSON string, enclosed in double quotes.DATE
,TIMESTAMP
andINTERVAL
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.
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 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_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.
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.
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.
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
andJSON_TABLE
SQL/JSON functions. Where appropriate these item methods support LOBs.
For more information see:
- TREAT(... AS JSON) in Oracle Database 18c
- JSON_EQUAL Condition in Oracle Database 18c
- JSON Data Guide Enhancements in Oracle Database 18c
- JSON_TABLE Enhancements in Oracle Database 18c
- Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...