8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- JSON-Relational Duality Views
- JSON_ARRAY Using Subqueries
- JSON Schema
- JSON Data Type Constructor Enhancements
- JSON_VALUE Function Enhancements
- JSON_SERIALIZE : ORDERED Keyword
- JSON_TRANSFORM Enhancement
- DBMS_JSON.JSON_TYPE_CONVERTIBLE_CHECK
- Loading JSON Data using External Tables
- Loading SODA Collections with SQL*Loader
- ORDS : AutoREST of JSON-Relational Duality Views
- Precheck Constraints using JSON Schema
- Predicates for JSON_QUERY and JSON_VALUE
- XML, JSON and Oracle Text Search Index Enhancements
Related articles.
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All Articles
JSON-Relational Duality Views
JSON-relational duality views expose our relational data as JSON documents, allowing both query and DML operations to be performed using conventional SQL or directly using JSON.
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.
JSON_SERIALIZE : ORDERED Keyword
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.
JSON_TRANSFORM Enhancement
In Oracle 23ai the JSON_TRANSFORM
function has lots of new operations, conditional control and richer path support.
DBMS_JSON.JSON_TYPE_CONVERTIBLE_CHECK
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.
For more information see:
- JSON-Relational Duality Views in Oracle Database 23ai
- JSON_ARRAY Using Subqueries in Oracle Database 23ai
- JSON Data Type Constructor Enhancements in Oracle Database 23ai
- JSON_VALUE Function Enhancements in Oracle Database 23ai
- JSON_SERIALIZED : ORDERED Keyword in Oracle Database 23ai
- JSON_TRANSFORM Enhancements in Oracle Database 23ai
- Migrating Text-Based JSON to JSON Data Type : Pre-Migration Check in Oracle Database 23ai
- Loading SODA Collections with SQL*Loader in Oracle Database 23ai
- Oracle REST Data Services (ORDS) : AutoREST of JSON-Relational Duality Views
- Precheck Constraints using JSON Schema in Oracle Database 23ai
- Predicates for JSON_QUERY and JSON_VALUE in Oracle Database 23ai
- XML, JSON and Oracle Text Search Index Enhancements in Oracle Database 23ai
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All Articles
Hope this helps. Regards Tim...