8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 18c » Here

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.

Related 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.

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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.