1. JSON Developer's Guide
  2. Changes in This Release for Oracle Database JSON Developer's Guide

Changes in This Release for Oracle Database JSON Developer's Guide

Changes in Oracle Database for this book are described.

Oracle Database JSON Developer's Guide was a new book in Oracle Database 12c Release 2 (12.2.0.1). Information about using JSON data in Oracle Database 12c Release 1 (12.1.0.2) is available in Oracle XML DB Developer’s Guide.

Changes in Oracle Database Release 18c, Version 18.1, for JSON Developer’s Guide

The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database Release 18c, Version 18.1, are described.

  • New Features
    The following features are new in this release.
  • Deprecated Features
    The following features are deprecated in this release, and may be desupported in a future release.

Parent topic: Changes in This Release for Oracle Database JSON Developer's Guide

New Features

The following features are new in this release.

Parent topic: Changes in Oracle Database Release 18c, Version 18.1, for JSON Developer’s Guide

SQL Enhancements

Multiple SQL enhancements are provided for storing and querying JSON data in the database.

You Can Specify That a SQL Expression Returns JSON Data

You can apply SQL function treat, with keywords AS JSON, to a SQL expression to specify that the expression returns JSON data. This is useful in situations where Oracle cannot determine that the result is JSON data. It is also useful in situations where you want to force some text to be interpreted as JSON data. For example, you can use it to tell Oracle to interpret a VARCHAR2 value of {} not as a string but as an empty JSON object.

See Also:

Oracle Database SQL Language Reference

LOB Results for JSON_VALUE, JSON_QUERY, and JSON_TABLE

SQL/JSON function json_value can now return a CLOB instance.

SQL/JSON function json_query can now return a CLOB or BLOB instance. A BLOB result is in the AL32UTF8 character set.

As before, the data type of a json_table column depends on whether the column is specified as FORMAT JSON. If it is, the json_query return types are supported; if it is not, the json_value return types are supported.

Previously:

  • json_value supported only VARCHAR2, NUMBER, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and SDO_GEOMETRY

    as return types.

  • json_query supported only VARCHAR2 as return type.

See Also:

Oracle Database SQL Language Reference

SQL/JSON Generation Function Enhancements

  • Optional keyword STRICT has been added. When present, any inputs that are declared or otherwise expected to be JSON data are checked to ensure that they are, in fact, well-formed JSON data. If any of them is not then an error is raised.

    (You can declare input to be JSON by using keywords FORMAT JSON or by using SQL function treat with keywords AS JSON. Input is expected to be JSON data if it is from a table column that has an is json constraint or it is the output of another JSON generation function.)

  • The SQL/JSON generation functions (json_object, json_array, json_objectagg, and json_arrayagg) can now return a CLOB or BLOB instance. A BLOB result is in the AL32UTF8 character set.

  • The input expression to a generation function can now be of any of these SQL data types (in addition to NUMBER, VARCHAR2, DATE and TIMESTAMP):

    • BINARY_DOUBLE

    • BINARY_FLOAT

    • CLOB

    • BLOB

    • NVARCHAR2

    • RAW

    • TIMESTAMP WITH TIME ZONE

    • TIMESTAMP WITH LOCAL TIME ZONE

    • INTERVAL YEAR TO MONTH

    • INTERVAL DAY TO SECOND

    How the result of generation is rendered in JSON depends on the data type of the input.

    See Also:

Item Methods for SQL/JSON Path Expressions

  • New item methods have been added: numberOnly(), stringOnly(), boolean(), booleanOnly(), size(), and type().

    The data-type conversion methods with “only” in their name are the same as the corresponding methods with names without “only”, except that the former convert only JSON values that are of the given type (e.g., number) to the related SQL data type (e.g. NUMBER). The methods without “only” in the name allow conversion, when possible, of any JSON value to the given SQL data type.

  • All item methods can now be used in path expressions for SQL/JSON functions json_value, json_query, and json_table (columns). In Oracle Database 12c Release 2 (12.2.0.1), item methods could only be used in path expressions for SQL/JSON condition json_exists.

  • Item method string() can now return CLOB or BLOB (in addition to VARCHAR2). The default is VARCHAR2(4000).

See Also:

SQL/JSON Path Expression Item Methods

JSON Data Guide Enhancements

  • Oracle SQL aggregate function json_dataguide now accepts optional formatting arguments:

    • You can return a hierarchical dataguide, by specifying keyword FORMAT with argument DBMS_JSON.FORMAT_HIERARCHICAL.

    • You can specify pretty-printing (indentation to improve readability) of the returned dataguide, by specifying keyword PRETTY with argument DBMS_JSON.PRETTY .

  • New data-dictionary views are available, to show you the dataguide information recorded for individual JSON object fields in a dataguide-enabled search index: USER_JSON_DATAGUIDE_FIELDS, ALL_JSON_DATAGUIDE_FIELDS, DBA_JSON_DATAGUIDE_FIELDS. (These are in addition to views USER_JSON_DATAGUIDES, ALL_JSON_DATAGUIDES, and DBA_JSON_DATAGUIDES, which list the tables that have JSON columns with a dataguide-enabled search index.)

  • For JSON documents that contain an array of scalar values, a dataguide now records the scalar types as well as the type ARRAY. The recorded path for the scalar values is indicated in a flat data guide by appending [*] to the path recorded for the array itself.

See Also:

Oracle Database SQL Language Reference

Data-Dictionary Views That Record the Presence of JSON Columns

Data-dictionary views USER_JSON_COLUMNS, ALL_JSON_COLUMNS, and DBA_JSON_COLUMNS now list the views, as well as the tables, that have columns with JSON data.

See Also:

Oracle Database Reference for information about ALL_JSON_COLUMNS and the related data-dictionary views

SQL/JSON Function JSON_TABLE Syntax

The syntax of json_table has been enhanced by making it simpler for some common use cases:

  • You can now use simple dot-notation syntax in place of a path expression.

  • If a column is the projection of a JSON object field, and if you want the column to have the same name as the field, then you need not provide a path expression to that object — the path is inferred from the column name.

See Also:

Oracle Database SQL Language Reference

ON STATEMENT Support For JSON_TABLE Materialized Views

You can now use keywords ON STATEMENT when creating a materialized view using a json_table query. Using ON STATEMENT instead of ON COMMIT means that the view is automatically synchronized for each DML statement against the base table.

New SQL Function TO_UTC_TIMESTAMP_TZ

SQL function to_UTC_timestamp_tz takes as input an ISO 8601 date format string and returns an instance of SQL data type TIMESTAMP WITH TIMEZONE. It normalizes the input to UTC time (Coordinated Universal Time, formerly Greenwich Mean Time). Unlike SQL function to_timestamp_tz, the new function assumes that the input string uses the ISO 8601 date format, defaulting the time zone to UTC 0.

A typical use of this function would be to provide its output to SQL function sys_extract_UTC, obtaining a UTC time that is then passed as a SQL bind variable to SQL/JSON condition json_exists, to perform a time-stamp range comparison.

See Also:

ISO 8601 for information about the ISO date formats

New Oracle SQL Condition JSON_EQUAL

Oracle SQL condition json_equal compares two JSON values and returns true if they are equal, false otherwise. For this comparison, insignificant whitespace and insignificant object member order are ignored. For example, JSON objects are equal if they have the same members, regardless of their order. However, if either of two compared objects has one or more duplicate fields then the value returned by json_equal is unspecified.

Sharding Support

Sharding support for JSON data has been enhanced.

The following are now supported:

  • JSON search index on sharded tables, whether user-managed or system-managed.

  • Cross-shard (and in-shard) queries for JSON data returned as CLOB or BLOB from SQL and PL/SQL. This includes projection of JSON data to CLOB and BLOB columns using SQL/JSON function json_table.

  • Evaluation on individual shards of cross-shard queries that use SQL/JSON functions and conditions. This includes the use of the following:

    • Function json_value and aggregate function json_dataguide in a SELECT clause

    • Function json_table in a FROM clause

    • Function json_value, and conditions json_exists and json_textcontains, in a WHERE clause.

  • Automatic relocation of base-table and index storage-table partitions during chunk migration.

See Also:

Oracle Sharding Guide

Parent topic: New Features

Performance Improvements for LOB Storage

Performance has been improved for JSON data that uses LOB storage. This particularly affects the common use case of reading and writing an entire JSON document stored as a LOB.

Parent topic: New Features

JSON Search Index Support for Longer Field Names

The maximum JSON field name length supported for a JSON search index is now 255 bytes (formerly it was 64 bytes).

Note:

You must rebuild any JSON search indexes and Oracle Text indexes created prior to Oracle Database 18c if they index JSON data that contains object fields with names longer than 64 bytes. See Oracle Database Upgrade Guide for more information.

Related Topics

Parent topic: New Features

Deprecated Features

The following features are deprecated in this release, and may be desupported in a future release.

  • SQL/JSON functions returning a Boolean JSON value as a number (zero or one). Return the value as VARCHAR2 or (in PL/SQL only) as BOOLEAN; do not return it as NUMBER. If you really need a SQL numeric value then you can use SQL DECODE or CASE WHEN to obtain zero or one from a VARCHAR2 value.

Parent topic: Changes in Oracle Database Release 18c, Version 18.1, for JSON Developer’s Guide

Changes in Oracle Database 12c Release 2 (12.2.0.1) for JSON Developer’s Guide

The changes in JSON support and in Oracle Database JSON Developer’s Guide for Oracle Database 12c Release 2 (12.2.0.1) are described.

  • New Features
    The following features are new in this release.

Parent topic: Changes in This Release for Oracle Database JSON Developer's Guide

New Features

The following features are new in this release.

Parent topic: Changes in Oracle Database 12c Release 2 (12.2.0.1) for JSON Developer’s Guide

Storage and Management of JSON Data

Parent topic: New Features

JSON Data Partitioning

You can now partition a table using a JSON virtual column as the partitioning key.

JSON Search Index on a Partitioned Table

You can now create a JSON search index on a partitioned base table (with range, list, hash, or interval partitioning).

Queries of JSON Data
  • Path Expression Enhancements
    JSON path expressions can now include filter expressions that must be satisfied by the matching data and transformation methods that can transform it.
  • Simple Dot-Notation Syntax Supports Array Access
    You can now access arrays and their elements using the simple dot-notation syntax.
  • Data Guide
    You can now create a JSON data guide, which captures the structural information of a set of JSON documents. It acts as a derived schema and is maintained along with the JSON data that it represents. It can also record statistics about scalar values used in the documents.
  • SQL/JSON Functions and Conditions Added to PL/SQL
    SQL/JSON functions json_value, json_query, json_object, and json_array, as well as SQL/JSON condition json_exists, have been added to the PL/SQL language as built-in functions (json_exists is a Boolean function in PL/SQL).
  • JSON_VALUE and JSON_TABLE Support for Additional Data Types
    You can now use SQL data types SDO_GEOMETRY, DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE with SQL/JSON functions json_value and json_table.

Parent topic: New Features

Path Expression Enhancements

JSON path expressions can now include filter expressions that must be satisfied by the matching data and transformation methods that can transform it.

Simple Dot-Notation Syntax Supports Array Access

You can now access arrays and their elements using the simple dot-notation syntax.

Parent topic: Queries of JSON Data

Data Guide

You can now create a JSON data guide, which captures the structural information of a set of JSON documents. It acts as a derived schema and is maintained along with the JSON data that it represents. It can also record statistics about scalar values used in the documents.

See Also:

JSON Data Guide

Parent topic: Queries of JSON Data

SQL/JSON Functions and Conditions Added to PL/SQL

SQL/JSON functions json_value, json_query, json_object, and json_array, as well as SQL/JSON condition json_exists, have been added to the PL/SQL language as built-in functions (json_exists is a Boolean function in PL/SQL).

JSON_VALUE and JSON_TABLE Support for Additional Data Types

You can now use SQL data types SDO_GEOMETRY, DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE with SQL/JSON functions json_value and json_table.

You can specify any of these as the return data type for SQL/JSON function json_value, and you can specify any of them as a column data type for SQL/JSON function json_table.

SDO_GEOMETRY is used for Oracle Spatial and Graph data. In particular, this means that you can use these functions with GeoJSON data, which is a format for encoding geographic data in JSON.

Performance

Parent topic: New Features

Search Enhancements

You can use a simpler syntax to create a JSON search index. Range search is now available for numbers and JSON strings that can be cast as built-in date and time types.

SQL/JSON Query Functions and Conditions Rewritten to JSON_TABLE

The optimizer will now often rewrite multiple invocations of json_exists, json_value, and json_query (any combination) to fewer invocations of json_table. This typically improves performance because the data is parsed only once for each json_table invocation.

JSON Columns In the In-Memory Column Store

You can now store JSON columns in the In-Memory Column Store, to improve query performance.

See Also:

In-Memory JSON Data

Parent topic: Performance

Materialized Views Over JSON Data

You can now create a materialized view over JSON data that is projected as VARCHAR2 or NUMBER columns.

SQL/JSON function json_table projects specific JSON data as VARCHAR2 or NUMBER columns. You can typically increase query performance by creating a materialized view over such columns. The view must be read-only: a FOR UPDATE clause is not allowed when creating it. Both full and incremental view refresh are supported. You can often increase query performance further by creating indexes on the view columns.

Other
  • SQL/JSON Functions for Generating JSON Data
    You can now construct JSON data programmatically using SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg.
  • PL/SQL APIs For JSON Data
    PL/SQL APIs are now available to provide (1) data guide operations and (2) get and set operations on JSON object types that are backed by an In-Memory, hierarchical, programmatic representation.
  • JSON Columns in a Sharded Table
    You can now create a JSON column in a sharded table and query that JSON data.

Parent topic: New Features

SQL/JSON Functions for Generating JSON Data

You can now construct JSON data programmatically using SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg.

See Also:

Generation of JSON Data

Parent topic: Other

PL/SQL APIs For JSON Data

PL/SQL APIs are now available to provide (1) data guide operations and (2) get and set operations on JSON object types that are backed by an In-Memory, hierarchical, programmatic representation.

JSON Columns in a Sharded Table

You can now create a JSON column in a sharded table and query that JSON data.

You can store JSON data in a column of type VARCHAR2 (up to 32,767 bytes), CLOB, or BLOB in a sharded table. You cannot query JSON data across multiple shards unless it is stored as VARCHAR2.

Parent topic: Other