SQL/JSON Function JSON

20 SQL/JSON Function JSON_QUERY

SQL/JSON function json_query selects one or more values from JSON data and returns those values. You can thus use json_query to retrieve fragments of a JSON document.

The JSON data that you query is the first argument to json_query. More precisely, it is a SQL expression that returns an instance of a SQL data type that contains JSON data: type JSON^Foot 1, VARCHAR2, CLOB, or BLOB. It can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting. The result of evaluating the expression is used as the context item for evaluating the path expression (described next).

The second argument to json_query is a SQL/JSON path expression followed by optional clauses RETURNING, PASSING, WRAPPER, QUOTES, ON ERROR, and ON EMPTY. The path expression can target any number of JSON values.

In a path-expression array step, each of the specified positions is matched against the data, in order, no matter how it is specified. The order of array indexes and ranges, multiple occurrences of an index, and duplication of a specified position due to range overlaps all matter.

In the RETURNING clause you can specify data type JSON, VARCHAR2, CLOB, or BLOB. A BLOB result is in the AL32UTF8 character set.

The default return type (no RETURNING clause) depends on the input data type. If the input type is JSON then JSON is also the default return type. Otherwise, VARCHAR2 is the default return type.

The value returned always contains well-formed JSON data. This includes ensuring that non-ASCII characters in string values are escaped as needed. For example, an ASCII TAB character (Unicode character CHARACTER TABULATION, U+0009) is escaped as \t. Keywords FORMAT JSON are not needed (or available) for json_query — JSON formatting is implicit for the return value.

The wrapper clause determines the form of the returned string value.

If (1) the JSON value to be returned by json_query is a string, and (2) the returning data type is textual, not JSON type, then the JSON string-delimiting double-quote characters are included in the returned value. In this context you must use keywords OMIT QUOTES if you want to omit the delimiting double-quote characters.

For example, if the return type is VARCHAR2 (the default, if the input data is not of JSON type), then the JSON string "hello", which has only the five characters hello, is returned as the seven-character VARCHAR2 value "hello".

Note:

You cannot use an array wrapper with json_query if you use clause OMIT QUOTES; a compile-time error is raised if you do that.

The error clause for json_query can specify EMPTY ON ERROR, which means that an empty array ([]) is returned in case of error (no error is raised).

If initialization parameter compatible is 20 or greater then Oracle Database supports IETF RFC 8259, which allows a JSON document to contain only a JSON scalar value at top level.

If parameter compatible is less than 20 then only RFC 4627 is supported. It allows only a JSON object or array, not a scalar, at the top level of a JSON document. RFC 8259 includes support for RFC 4627 (and RFC 7159).

If RFC 8259 is not supported, and if the value targeted by a json_query path-expression argument targets multiple values or a single scalar value, then you must use keywords WITH WRAPPER to return the value(s) wrapped in an array. Otherwise, an error is raised.

If RFC 8259 is supported then json_query can return scalar JSON values, by default. To require json_query to return only nonscalar JSON values, use keywords DISALLOW SCALARS in the RETURNING clause. In that case the behavior is the same as if RFC 8259 were not supported — you must use WITH WRAPPER.

Example 20-1 shows an example of using SQL/JSON function json_query with an array wrapper. For each document it returns a VARCHAR2 value whose contents represent a JSON array with elements the phone types, in an unspecified order. For the document in Example 4-3 the phone types are "Office" and "Mobile", and the array returned is either [ "Mobile", "Office" ] or [ "Office", "Mobile" ].

Note that if path expression $.ShippingInstructions.Phone.type were used in Example 20-1 it would give the same result. Because of SQL/JSON path-expression syntax relaxation, [*].type is equivalent to .type.

20.1 JSON_QUERY as JSON_TABLE

SQL/JSON function json_query can be viewed as a special case of function json_table.

Example 20-2 illustrates the equivalence: the two SELECT statements have the same effect.

In addition to perhaps helping you understand json_query better, this equivalence is important practically, because it means that you can use either function to get the same effect.

In particular, if you use json_query more than once, or you use it in combination with json_exists or json_value (which can also be expressed using json_table), to access the same data, then a single invocation of json_table presents the advantage that the data is parsed only once.

Because of this, the optimizer often automatically rewrites multiple invocations of json_exists, json_value and json_query (any combination) to fewer invocations of json_table.

Note:

You can use SQL hint NO_JSON_TABLE_TRANSFORM to prevent rewriting of multiple invocations of json_exists, json_value and json_query (any combination) to fewer invocations of json_table.

Example 20-2 JSON_QUERY Expressed Using JSON_TABLE

The keywords FORMAT JSON are used only if data_type is not JSON type. (Keywords FORMAT JSON cannot be used with JSON type.)

SELECT json_query(column, json_path
                  RETURNING data_type array_wrapper
                            error_handler ON ERROR)
  FROM table;

SELECT jt.column_alias
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" data_type FORMAT JSON array_wrapper
                  PATH json_path)) AS "JT";

Related Topics

JSON_TABLE Generalizes SQL/JSON Query Functions and Conditions

Parent topic: SQL/JSON Function JSON_QUERY

Footnote Legend

Footnote 1: Database initialization parameter compatible must be at least 20 to use data type JSON.