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

Home » Articles » 23 » Here

JSON Support Enhancements in Oracle Database 23ai

Oracle 23ai includes several enhancements to the JSON functionality in the database. This post acts as a links page to separate articles on each feature.

Related articles.

JSON_ARRAY Using Subqueries

From Oracle database 23ai onward the JSON_ARRAY function can accept a subquery as input, which brings it in line with the current SQL/JSON standard.

JSON Schema

In Oracle 23ai a JSON Schema can validate the structure and contents of JSON documents in your database.

JSON Data Type Constructor Enhancements

The JSON data type constructor can now accept collections, object types and record types as input.

JSON_VALUE Function Enhancements

The JSON data type constructor can now accept collections, object types and record types as input.


In Oracle 23ai the ORDERED keyword has been added to the JSON_SERIALIZE function. When present, the members are serialized in ascending alphabetical order by field name.


In Oracle 23ai the JSON_TRANSFORM function has lots of new operations, conditional control and richer path support.


In Oracle 23ai the JSON_TYPE_CONVERTIBLE_CHECK procedure has been added to the DBMS_JSON package to allow pre-migration checks to be performed on text-based JSON columns.

Loading JSON Data using External Tables

In Oracle database 23ai loading JSON data using external tables got much simpler.

Loading SODA Collections with SQL*Loader

In Oracle 23ai we can use SQL*Loader to load JSON data into a Simple Oracle Document Access (SODA) collection.

ORDS : AutoREST of JSON-Relational Duality Views

The AutoREST functionality of Oracle REST Data Services (ORDS) has been extended to include JSON-relational duality views in Oracle 23ai.

Precheck Constraints using JSON Schema

In Oracle database 23ai we can use the PRECHECK keyword to mark check constraints as being validated externally by an application.

Predicates for JSON_QUERY and JSON_VALUE

In Oracle database 23ai the JSON_QUERY and JSON_VALUE functions can include multiple predicates in a single JSON path expression, and use the PASSING clause to support variables.

XML, JSON and Oracle Text Search Index Enhancements

In Oracle 23ai the CREATE SEARCH INDEX statement allows us to create search indexes on XML, JSON and text data, making the syntax consistent between them.


Oracle database 23ai has introduced the ability to convert nulls to empty strings during JSON generation.


By default many JSON functions return null if they encounter a runtime error. Oracle database 23ai introduced the JSON_BEHAVIOR parameter to allow us to alter this default behaviour for our session.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.