JSON
JSON-Relational Duality
JSON Relational Duality Views are fully updatable JSON views over relational data. Data is still stored in relational tables in a highly efficient normalized format but can be accessed by applications in the form of JSON documents.
Duality views provide you with game-changing flexibility and simplicity by overcoming the historical challenges developers have faced when building applications using relational or document models.
JSON Schema
JSON Schema-based validation is allowed with the SQL condition IS JSON and with a PL/SQL utility function. A JSON schema is a JSON document that specifies allowed properties (field names) and the corresponding allowed data types, and whether they are optional or mandatory.
By default, JSON data is schemaless, providing flexibility. However, you may want to ensure that your JSON data contains particular mandatory fixed structures and typing, besides other optional and flexible components, which can be done via JSON Schema validation.
XML and JSON Search Index Enhancements
The Oracle Text XML search index syntax and JSON search index syntax are now consistent. Additionally, the performance of JSON and XML search indexes has been improved.
Using the same syntax for XML or JSON search indexes and better performance increase productivity.
GoldenGate Replication of JSON-Relational Duality Views
This feature allows developers to use Oracle GoldenGate technology to replicate JSON-relational duality view data as JSON documents, instead of relational tables, from an Oracle AI Database to a target Oracle or non-Oracle database.
Replication of JSON data is an important feature for high availability, fail-over, and real-time migration from a non-Oracle database to Oracle AI Database.
Oracle GoldenGate Replication to non-Oracle databases such as MongoDB (a document database) or Redis (a NoSQL key/value store) is simple and performant with the ability to replicate JSON documents from JSON-relational duality views.
Developers need not write complex JSON and SQL transformations to shape data for relational and document-based updates, or pay the cost of reconstructing application objects on the target database.
JSON-Relational Duality Views: Hidden and Generated Fields
You can map duality view data to hidden fields, absent from the view's documents. A generated field can use the value of hidden fields.
A document supported by a duality view can be simpler if it need not have a field for every underlying column, in particular for columns needed only for calculating other field values.
JSON-Relational Duality Views: Add Fields With Calculated Values
Duality views generate JSON documents containing objects whose fields map to columns of relational tables. You can add object fields whose values are calculated automatically.
When the database automatically augments objects by adding calculated fields, such calculation need not be done by multiple, separate client applications. And field calculations can refer to stored data that is not exposed in the resulting objects - a feature similar to redaction.
JSON Collection Views
JSON collection views are special, read-only database views that expose JSON objects in a JSON-type column named DATA.
JSON collection views are conceptually close to JSON-relational duality views, but they have fewer restrictions because they are read only.
JSON Replication
New options in the logical_replication_clause of the CREATE and ALTER TABLE statements let you control how JSON changes are logged. Instead of always logging entire JSON documents, you can now choose to log only the modified portions (PARTIAL JSON) or continue logging full documents (NO PARTIAL JSON).
Partial JSON logging improves replication efficiency by capturing and transferring only the changed portions of JSON data. This reduces network and storage overhead, accelerates replication, and enables better performance on existing hardware—lowering overall infrastructure costs.
JSON Search Index Path Subsetting
When creating a JSON search index you can specify the fields to include or exclude from indexing: path subsetting.
Path subsetting can reduce the size of a search index and improve its performance.
Replication Support for JSON Collection Tables
JSON Collection Tables can be enabled for logical replication using GoldenGate. Replication is supported to and from JSON Relational Duality Views as well to and from third-party products, such as MongoDB.
Replication is a basic database functionality that works between Oracle databases. It is also used to facilitate online migration to Oracle AI Database from third-party databases, such as MongoDB.
Additional Flexibility Defining JSON-Relational Duality Views
You can use field _id in document subobjects to identify a column that selects document, and use an identity column as an identifying column.
These additional possibilities when defining a duality view provide more kinds of documents that can be supported, and thus allow for more application use cases.
Changes for JSON Search Index and Data Guide
JSON search index and JSON data guide are enhanced in these ways. The first two represent changes in the default behavior.
- When creating a JSON search index, by default a data guide is not created.
- By default,
DBMS_JSONprocedurescreate_view,get_view_sql, andadd_virtual_columnsresolve name conflicts; that is, the default value of parameterresolveNameConflictsisTRUE, notFALSE. This means that if a resulting field name exists in the same data guide then it is suffixed with a new sequence number, to make it unique. - Function
json_dataguideis enhanced to detect ISO 8601 date-time string values, using flag optionDBMS_JSON.detect_datetime.
When this option is present, field values that are strings in the ISO 8601 date and time formats supported by Oracle are represented in a data guide with the value of field type not as string but as timestamp.
The default changes improve usability and performance for JSON data guides.
Comparing and Sorting JSON Data Types
JSON data type can now be used directly in a WHERE, ORDER BY, and GROUP BY clause.
The broader applicability of the JSON data type in SQL constructs simplifies your application development and improves the performance of your applications by avoiding the need for explicit casts.
DBMS_AQ Support for JSON Arrays
You can use a JSON data type array as the payload for Advanced Queuing (AQ) message-passing functions, which process an array of messages as a single operation. This applies to the AQ interfaces for C (Oracle Call Interface), PL/SQL, and Java (JDBC).
Advanced Queuing can directly use JSON data for its bulk message passing. With JSON being an increasingly popular format for data exchange, this functionality provides more flexible application development and improves developer productivity.
EMPTY STRING ON NULL for JSON Generation
When generating JSON data from relational data, a SQL NULL input value results in a JSON null value by default.
In Oracle SQL, a SQL NULL value cannot be distinguished from an empty string value (''). This means that an empty SQL string input is treated the same as SQL NULL. This behavior can sometimes confuse users.
When using a SQL/JSON generation function such as json_object, for NULL input values of a SQL character data type, such as CLOB and VARCHAR2, a user can specify that an empty JSON string ("") be created. The same is true for function json_scalar.
With this feature, generating a JSON empty string ("") from an empty SQL string is easy and efficient. Without this feature, a user needs to use a complex CASE statement to do the same.
Enhancement to JSON_TRANSFORM
JSON_TRANSFORM is extended to support right-hand-side path expressions, nested paths, and arithmetic operations. A SORT operator is supported which allows sorting the elements in an array.
JSON_TRANSFORM is the main SQL operator for modifying JSON data, both for on-disk updates and transient changes in the SELECT clause of a query. This enhancement increases update capabilities, such as arithmetic calculations and operations on nested arrays and raises developer productivity.
Enhancements to GraphQL Syntax for Duality View Creation
These syntax enhancements are provided for using GraphQL with JSON-relational duality views:
- Comments
- New directives:
@cast,@object, and@array - Enhanced directives:
@generated,@link, and@where - Support for fully qualified table/column names
- Query-By-Example (QBE) predicates for filtering (analogous to SQL
WHERE) - Simplified QBE using query arguments as predicates
These enhancements allow more flexible use of GraphQL when creating a duality view.
JSON Collections
JSON collections are special tables or views that store (or represent) only JSON documents in a document-store-compatible format, such as the Oracle AI Database API for MongoDB. JSON collections are integrated into the database and fully operable with SQL, from creation to manipulation and query processing. For example, it is possible to do a simple INSERT AS SELECT into a JSON collection table.
JSON collection tables complement JSON Duality Views, the marquee JSON collection views that provide the benefits of relational storage and JSON document processing with a single database structure.
Native JSON collections simplify working with JSON data stored in collections within the Oracle AI Database ecosystem. For example, with collections, you easily analyze your JSON documents with SQL while concurrently using those operationally with document-centric APIs, such as the Oracle AI Database API for MongoDB.
JSON Data Guide Format FORMAT_SCHEMA
Format FORMAT_SCHEMA produces a data guide that you can use to validate JSON documents.
You can produce JSON data guide documents that you can use to validate JSON documents.
JSON Type Modifier Enhancements: Data Size and Array Specifications
You can specify the maximum size in bytes for a JSON-type column.
For type modifier ARRAY, you can specify:
- A (single) scalar type for all of the array elements
- Whether array elements can be JSON null
- The maximum number of array elements
- Whether to store array elements sorted in ascending order
Specifying a size limit for a JSON-type column and constraining array size, element type, and storage order can lead to more efficient handling of the stored JSON data.
JSON Type Modifiers
You can limit the kinds of values stored in a JSON-type column to objects, arrays, scalars, or a combination, by adding modifier keywords OBJECT, ARRAY, and SCALAR to the column specification when creating a table. For example, DATA JSON (OBJECT) ensures that only JSON objects can be stored.
Using modifier OBJECT alone ensures that the column data is compatible with documents in Oracle JSON collections and in typical document databases.
JSON Type Support for External Tables
Support for access and direct-loading of JSON-type columns is provided for external tables. JSON data type is supported as a column type in the external table definition. Newline-delimited and JSON-array file options are supported, which facilitates importing JSON data from an external table.
This feature makes it easier to load data into a JSON-type columns.
JSON to Duality Migrator: Hints Configuration Field
The hints configuration field for duality views can map primary-key columns to subobject fields, map SQL data types to fields, and disable sharing of data underlying a subobject.
You can obtain more control over the mapping between the documents supported by duality view and their underlying relational data:
- Which document fields should correspond to primary-key columns
- Which SQL data types should underlie particular fields
- Whether to normalize particular objects in a document
This feature enhances the ability of developers to leverage JSON documents for data access using the highly efficient relational data storage model, without compromising simplicity or efficiency.
JSON to Duality Migrator: Multi-Collection Import API
DBMS_JSON_DUALITY.IMPORT_ALL is a PL/SQL procedure that is designed to import multiple document collections into a JSON-relational duality view.
While the existing DBMS_JSON_DUALITY.IMPORT procedure is limited to importing a single collection, the ability to import multiple collections in a single PL/SQL call simplifies the process and helps prevent constraint violation errors. The IMPORT_ALL procedure enables the efficient import of multiple document collections into a JSON-relational duality view.
JSON to Duality Migrator: Schema Inference using JSON Schema
This feature allows the JSON to Duality Migrator to use user provided JSON schemas for relational schema inference. The JSON schemas can be provided in isolation or in conjunction with data for more accurate schema inference.
This feature makes the JSON to Duality Migrator more accessible to customers who may not have a large amount of JSON data for the Migrator to analyze or who do not want to send confidential data to the Migrator. As long as they have a JSON schema for each collection, they can still use the Migrator.
JSON to Duality Migrator: Validation of Schema and Data
The PL/SQL functions DBMS_JSON_DUALITY.VALIDATE_SCHEMA_REPORT and VALIDATE_IMPORT_REPORT are provided to validate the relational schema and data that are created and imported by the JSON-To-Duality Migrator.
There is a growing need for functions that would validate the recommended relational schema. To address these needs, the validation APIs have been developed that:
- Help users verify accuracy of the recommended relational schema
- Confirms that no data is lost when they migrate their document collections to duality views.
JSON-to-Duality Converter
Given an existing set of JSON collections as input, the JSON-To-Duality Converter creates a set of JSON-relational duality views, based on normalized relational schemas, that support the same document collections. This creation needs no user supervision, but users can override schema recommendations.
This feature provides one part of the JSON-to-Duality Migrator, which is a set of PL/SQL procedures to move document-centric applications and their JSON documents from a document database to duality views in Oracle AI Database.
JSON-to-Duality Importer
This feature imports application data from a set of JSON collections into JSON-relational duality views that have been created using the JSON-to-Duality Converter.
This feature provides one part of the JSON-to-Duality Migrator, which is a set of PL/SQL procedures to move document-centric applications and their JSON documents from a document database to duality views in Oracle AI Database.
JSON/JSON_VALUE will Convert PL/SQL Aggregate Type to/from JSON
The PL/SQL JSON constructor is enhanced to accept an instance of a corresponding PL/SQL aggregate type, returning a JSON object or array type populated with the aggregate type data.
The PL/SQL JSON_VALUE operator is enhanced so that its returning clause can accept a type name that defines the type of the instance that the operator is to return.
JSON constructor support for aggregate data types streamlines data interchange between PL/SQL applications and languages that support JSON.
JSON_ARRAY Constructor by Query
A subquery can be used as an argument to SQL/JSON function JSON_ARRAY to define the array elements. This functionality is part of the SQL/JSON standard.
This feature increases your developer productivity and higher interoperability with other SQL/JSON standard-compliant solutions.
JSON_BEHAVIOR Initialization Parameter to Control SQL/JSON Runtime Behavior
You can use the initialization parameter JSON_BEHAVIOR to change the default behavior of SQL/JSON operators within a database session.
You can change the default return data type, default type-compatibility, and default error behavior during a session for applicable SQL/JSON operators.
This lets you enforce consistent session-level JSON processing behavior, reducing the need for explicit overriding of default behaviors within individual SQL statements.
JSON_EXPRESSION_CHECK Parameter
A new parameter JSON_EXPRESSION_CHECK allows to enable/disable a JSON query check. The values are on and off. The default is off. For now, this parameter is limited to JSON-relational duality views. An error is raised if a JSON path expression on a duality view does not match to an underlying column, for example if the path expression has a typo. The error is raised during query compilations.
This simplifies working with JSON-relational duality views, as incorrect JSON path expressions do not need to be debugged at runtime but instead are flagged at query compilation time (by raising an error).
JSON_ID SQL Operator
SQL operator JSON_ID generates a unique document-identifier value, for unique access to JSON documents in a collection. The argument to JSON_ID determines whether the value is a 12-byte OID or a 16-byte UUID. In Oracle JSON collections, JSON_ID is used to create (automatically or explicitly) the values for document-identifier field _id.
JSON_ID simplifies the generation of ID values to uniquely identify JSON documents.
JSON_TRANSFORM Operators ADD_SET and REMOVE_SET
Oracle SQL function JSON_TRANSFORM operators ADD_SET and REMOVE_SET work with JSON arrays as if they are sets; that is, as if their elements are unordered and unique (no duplicates).
- Operator
ADD_SETadds a value to an array only if the value is not already an element. - Operator
REMOVE_SETremoves all occurrences of a given value from an array.
Application code can more concisely update arrays that it uses as sets.
LOBs Returned by SQL Functions for JSON can be Value-Based
Wherever a SQL function for JSON returns a LOB value, the returning clause can specify that the LOB be value-based. By default, a LOB reference is returned instead. For example:
JSON_SERIALIZE (data returning CLOB VALUE)
Value-based LOBs are are easier to use because they do not need to be freed explicitly. The database fully manages the lifecycle of value-based LOBs and frees them when appropriate.
New JSON Data Dictionary Views
New dictionary views *_JSON_INDEXES and *_TABLE_VIRTUAL_COLUMNS have been added.
These new views provide better insight into the database objects that have been created to work with JSON data.
ORDERED in JSON_SERIALIZE
The SQL function JSON_SERIALIZE has an optional keyword ORDERED, which reorders the key-value pairs alphabetically (ascending only). It can be combined with optional keywords PRETTY and ASCII.
Ordering the result of serialization makes it easier for both tools and humans to compare values.
Precheckable Constraints using JSON SCHEMA
To avoid sending invalid data to the database, an application can often precheck (validate) it. PL/SQL function DBMS_JSON_SCHEMA.describe provides JSON schemas that apps can use to perform validation equivalent to that performed by database column-level check constraints, and it records constraints that have no equivalent JSON schema.
Applications can also check which columns are precheckable with a JSON schema by consulting static dictionary views ALL_CONSTRAINTS, DBA_CONSTRAINTS, and USER_CONSTRAINTS.
When you create or alter a table you can use keyword PRECHECK to determine whether column check constraints can be prechecked outside the database. If no equivalent JSON schema exists for a given PRECHECK column check constraint then an error is raised.
Early detection of invalid data makes applications more resilient and reduces potential system downtime. All applications have access to the same information about whether data for a given column is precheckable, and if so what JSON schema validates it.
Predicates for JSON_VALUE and JSON_QUERY
JSON path expressions with predicates can be used in JSON_VALUE and JSON_QUERY. The functionality is part of the SQL/JSON standard.
Applying JSON path expressions more widely for querying JSON data boosts your developer's productivity and simplifies code development.
SCORE Ancillary Operator for JSON_TEXTCONTAINS()
This feature allows you to return a score for your JSON_TEXTCONTAINS() queries by using the SCORE() operator.
You can also order the results by the score.
JSON_TEXTCONTAINS function gains a new parameter for use with the SCORE() function allowing for an improved development experience.
SODA Enhancements
Various extensions are made to the SODA API:
- Merge and patch: New SODA operations mergeOne and mergeOneAndGet.
- Embedded Keys: You can now embed the key of a document in the document itself. This is used for MongoDB-compatible collections.
- Dynamic Data Guide: The operation to compute a data guide on the fly is extended to other SODA languages, besides PL/SQL and C.
- Sampling operation: The sampling operation is extended to other SODA languages, besides PL/SQL and C.
- Flashback: The operation to use flashback is extended to other SODA languages, besides PL/SQL and C.
- Hints and monitoring: Hints and SQL monitoring are extended to other SODA languages, besides PL/SQL and C.
- Explain plan: Obtaining a SQL execution plan is extended to other SODA languages, besides PL/SQL and C.
- Data Guard and Golden Gate: You can now replicate SODA collections using Oracle Data Guard and Oracle GoldenGate.
- Index Discovery: You can now fetch all indexes for a given SODA collection.
- Multivalue index creation: New SODA APIs for PL/SQL, C, and Java to create multivalue indexes.
These extensions increase the usability and capabilities of SODA in general, thus improving developer productivity.
Tools to Migrate JSON Text Storage to JSON Type Storages
The new PL/SQL procedure, DBMS_JSON.json_type_convertible_check, checks whether existing data stored as JSON text can be migrated to JSON data type. There are several alternative ways to migrate the data after this check succeeds.
Leveraging the binary JSON data type format provides the best performance for processing JSON data. Providing a simple and easy way to ensure existing data can be transformed successfully to binary JSON format helps you to adopt the preferred storage format for JSON data.
WHERE Clauses in JSON-Relational Duality Views
When creating a JSON-relational duality view you can use simple WHERE clauses to limit the rows from which to generate JSON data from underlying tables. As one kind of use case, you can create multiple duality views, whose documents contain different data depending on the values in a discriminating column. For example, with the same underlying table you can define views for data from different countries, using a WHERE clause that selects only table rows whose country-code column has a given value (for example, FR for France). The JSON documents supported by a country view reflect this requirement, and the requirement is enforced for updates.
WHERE clauses in view definitions allow fine-grained control of the data that is to be included in a JSON document supported by a duality view.