8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON Support Enhancements in Oracle Database 23c
Oracle 23c 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 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, 23c, 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 23c 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 23c 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 23c 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 23c the JSON_TRANSFORM
function has lots of new operations, conditional control and richer path support.
DBMS_JSON.JSON_TYPE_CONVERTIBLE_CHECK
In Oracle 23c 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 SODA Collections with SQL*Loader
In Oracle 23c 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 23c.
Precheck Constraints using JSON Schema
In Oracle database 23c 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 23c 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 23c 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 23c
- JSON_ARRAY Using Subqueries in Oracle Database 23c
- JSON Data Type Constructor Enhancements in Oracle Database 23c
- JSON_VALUE Function Enhancements in Oracle Database 23c
- JSON_SERIALIZED : ORDERED Keyword in Oracle Database 23c
- JSON_TRANSFORM Enhancements in Oracle Database 23c
- Migrating Text-Based JSON to JSON Data Type : Pre-Migration Check in Oracle Database 23c
- Loading SODA Collections with SQL*Loader in Oracle Database 23c
- Oracle REST Data Services (ORDS) : AutoREST of JSON-Relational Duality Views
- Precheck Constraints using JSON Schema in Oracle Database 23c
- Predicates for JSON_QUERY and JSON_VALUE in Oracle Database 23c
- XML, JSON and Oracle Text Search Index Enhancements in Oracle Database 23c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...