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.

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.

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:

  • Handling of Input Values For SQL/JSON Generation Functions

  • Oracle Database SQL Language Reference

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).

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.

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.

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.

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.

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.