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.
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.
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 Modifiers
A JSON type column can store any JSON, this includes JSON objects, arrays and scalars. There are cases where a user would want to make sure that a JSON type is always an object. For this, we added type modifiers, for example, data JSON (object).
This feature allows the user to specify the top level type of a JSON (object, array, scalar).
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 Converter
Given an existing set of JSON collections as input, this 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 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 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 Parameter to Override ON ERROR Default
The new JSON_BEHAVIOR initialization parameter allows you to override the default ON ERROR handler.
JSON_BEHAVIOR=ON_ERROR:ERROR
JSON_BEHAVIOR=ON_ERROR:NULL
Overriding the NULL ON ERROR default to ERROR ON ERROR makes sure that queries during development time have no typos in the path expression.
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_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.