JSON_QUERY

Note:

The JSON_QUERY function is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Syntax

Description of the illustration json_query.gif

JSON_path_expression::=

Description of the illustration json_path_expression.gif

object_step::=

Description of the illustration object_step.gif

array_step::=

Description of the illustration array_step.gif

JSON_query_returning_clause::=

Description of the illustration json_query_returning_clause.gif

JSON_query_return_type::=

Description of the illustration json_query_return_type.gif

JSON_query_wrapper_clause::=

Description of the illustration json_query_wrapper_clause.gif

JSON_query_on_error_clause::=

Description of the illustration json_query_on_error_clause.gif

Purpose

JSON_QUERY finds one or more specified JSON values in JSON data and returns the values in a character string.

expr

Use this clause to specify the JSON data to be evaluated. For expr, specify an expression that evaluates to a text literal. If expr is a column, then the column must be of data type VARCHAR2, CLOB, or BLOB. If expr is null, then the function returns null.

If expr is not a text literal of well-formed JSON data using strict or lax syntax, then the function returns null by default. You can use the JSON_query_on_error_clause to override this default behavior. Refer to JSON_query_on_error_clause.

FORMAT JSON

You must specify FORMAT JSON if expr is a column of data type BLOB.

JSON_path_expression

Use this clause to specify a JSON path expression. The function uses the path expression to evaluate expr and find one or more JSON values that match, or satisfy, the path expression. The path expression must be a text literal.

The path expression must begin with a dollar sign ($), which represents the context item, that is, the expression specified by expr. The dollar sign is followed by zero or more steps, each of which can be an object step or an array step.

The function attempts to match the first step in the path expression to the context item. If the first step results in a match, then the function attempts to match the second step to the JSON value(s) that matched the first step. If the second step results in a match, then the function attempts to match the third step to the JSON values(s) that matched the second step, and so on. The function returns the value(s) matched in the final step as a comma-separated sequence of values in a character string. The order of the sequence is nondeterministic. All values are returned using strict JSON syntax, regardless of whether the original JSON data used strict or lax JSON syntax. A path expression that consists of a dollar sign followed by zero steps ('$') matches the entire context item.

You can specify the JSON_query_returning_clause to control the data type and format of the return character string. Refer to the JSON_query_returning_clause.

If multiple values match the path expression, or if only one scalar value matches the path expression, then you must wrap the value(s) in an array wrapper. Refer to the JSON_query_wrapper_clause.

If any step in the path expression does not result in a match, then the function returns null by default. You can use the JSON_query_on_error_clause to override this default behavior. Refer to the JSON_query_on_error_clause.

object_step Use this clause to specify an object step.

Use simple_name or complex_name to specify a property name. If a member with that property name exists in the JSON object being evaluated, then the object step results in a match to the property value of that member. Otherwise, the object step does not result in a match. Both types of names are case-sensitive. Therefore, a match will result only if the alphabetic character cases match in the object step and the JSON data.

A simple_name can contain only alphanumeric characters and must begin with an alphabetic character. A complex_name can contain only alphanumeric characters and spaces, and must begin with an alphanumeric character. A complex_name must be enclosed in double quotation marks.
Use the asterisk wildcard symbol (*) to specify all property names. If the JSON object being evaluated contains at least one member, then the object step results in a match to the values of all members. Otherwise, the object step does not result in a match.

If you apply an object step to a JSON array, then the array is implicitly unwrapped and the elements of the array are evaluated using the object step. This is called JSON path expression relaxation. Refer to Oracle XML DB Developer's Guide for more information.

If the JSON data being evaluated is not a JSON object, then the object step does not result in a match.

array_step Use this clause to specify an array step.

Use integer to specify the element at index integer in a JSON array. Use integer TO integer to specify the range of elements between the two index integer values, inclusive. If the specified elements exist in the JSON array being evaluated, then the array step results in a match to those elements. Otherwise, the array step does not result in a match. The first element in a JSON array had index 0.
Use the asterisk wildcard symbol (*) to specify all elements in a JSON array. If the JSON array being evaluated contains at least one element, then the array step results in a match to all elements in the JSON array. Otherwise, the array step does not result in a match.

If the JSON data being evaluated is not a JSON array, then the data is implicitly wrapped in an array and then evaluated using the array step. This is called JSON path expression relaxation. Refer to Oracle XML DB Developer's Guide for more information.

JSON_query_returning_clause

Use this clause to specify the data type and format of the character string returned by this function.

RETURNING Use the RETURNING clause to specify the data type of the character string. If you omit this clause, then JSON_QUERY returns a character string of type VARCHAR2(4000).

You can use the JSON_return_type_clause to specify the following data type:

VARCHAR2[(size [BYTE,CHAR])]

When specifying the VARCHAR2 data type elsewhere in SQL, you are required to specify a size. However, in this clause you can omit the size. In this case, JSON_QUERY returns a character string of type VARCHAR2(4000).

Refer to "VARCHAR2 Data Type" for more information.

If the data type is not large enough to hold the return character string, then this function returns null by default. You can use the JSON_query_on_error_clause to override this default behavior. Refer to the JSON_query_on_error_clause.

PRETTY Specify PRETTY to pretty-print the return character string by inserting newline characters and indenting.

ASCII Specify ASCII to automatically escape any non-ASCII Unicode characters in the return character string, using standard ASCII Unicode escape sequences.

JSON_query_wrapper_clause

Use this clause to control whether this function wraps the values matched by the path expression in an array wrapper—that is, encloses the sequence of values in square brackets ([]).

Specify WITHOUT WRAPPER to omit the array wrapper. You can specify this clause only if the path expression matches a single JSON object or JSON array. This is the default.
Specify WITH WRAPPER to include the array wrapper. You must specify this clause if the path expression matches a single scalar value (a value that is not a JSON object or JSON array) or multiple values of any type.
Specifying the WITH UNCONDITIONAL WRAPPER clause is equivalent to specifying the WITH WRAPPER clause. The UNCONDITIONAL keyword is provided for semantic clarity.
Specify WITH CONDITIONAL WRAPPER to include the array wrapper only if the path expression matches a single scalar value or multiple values of any type. If the path expression matches a single JSON object or JSON array, then the array wrapper is omitted.

The ARRAY keyword is optional and is provided for semantic clarity.

If the function returns a single scalar value, or multiple values of any type, and you do not specify WITH [UNCONDITIONAL | CONDITIONAL] WRAPPER, then the function returns null by default. You can use the JSON_query_on_error_clause to override this default behavior. Refer to the JSON_query_on_error_clause.

JSON_query_on_error_clause

Use this clause to specify the value returned by this function when any of the following errors occur:

expr is not well-formed JSON data using strict or lax JSON syntax
No match is found when the JSON data is evaluated using the JSON path expression
The return value data type is not large enough to hold the return character string
The function matches a single scalar value or, multiple values of any type, and the WITH [UNCONDITIONAL | CONDITIONAL] WRAPPER clause is not specified

You can specify the following clauses:

NULL ON ERROR - Returns null when an error occurs. This is the default.
ERROR ON ERROR - Returns the appropriate Oracle error when an error occurs.
EMPTY ON ERROR - Returns an empty JSON array ('[]') when an error occurs.

Examples

The following query returns the context item, or the specified string of JSON data. The path expression matches a single JSON object, which does not require an array wrapper. Note that the JSON data is converted to strict JSON syntax in the returned value—that is, the object property names are enclosed in double quotation marks.

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$') AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
{"a":100,"b":200,"c":300}

The following query returns the value of the member with property name a. The path expression matches a scalar value, which must be enclosed in an array wrapper. Therefore, the WITH WRAPPER clause is specified.

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.a' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[100]

The following query returns the values of all object members. The path expression matches multiple values, which together must be enclosed in an array wrapper. Therefore, the WITH WRAPPER clause is specified.

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.*' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[100,200,300]

The following query returns the context item, or the specified string of JSON data. The path expression matches a single JSON array, which does not require an array wrapper.

SELECT JSON_QUERY('[0,1,2,3,4]', '$') AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[0,1,2,3,4]

The following query is similar to the previous query, except the WITH WRAPPER clause is specified. Therefore, the JSON array is wrapped in an array wrapper.

SELECT JSON_QUERY('[0,1,2,3,4]', '$' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[[0,1,2,3,4]]

The following query returns all elements in a JSON array. The path expression matches multiple values, which together must be enclosed in an array wrapper. Therefore, the WITH WRAPPER clause is specified.

SELECT JSON_QUERY('[0,1,2,3,4]', '$[*]' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[0,1,2,3,4]

The following query returns the elements at indexes 0, 3 through 5, and 7 in a JSON array. The path expression matches multiple values, which together must be enclosed in an array wrapper. Therefore, the WITH WRAPPER clause is specified.

SELECT JSON_QUERY('[0,1,2,3,4,5,6,7,8]', '$[0, 3 TO 5, 7]' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[0,3,4,5,7]

The following query returns the fourth element in a JSON array. The path expression matches a scalar value, which must be enclosed in an array wrapper. Therefore, the WITH WRAPPER clause is specified.

SELECT JSON_QUERY('[0,1,2,3,4]', '$[3]' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[3]

The following query returns the first element in a JSON array. The WITH CONDITIONAL WRAPPER clause is specified and the path expression matches a single JSON object. Therefore, the value returned is not wrapped in an array. Note that the JSON data is converted to strict JSON syntax in the returned value—that is, the object property name is enclosed in double quotation marks.

SELECT JSON_QUERY('[{a:100},{b:200},{c:300}]', '$[0]'
       WITH CONDITIONAL WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
{"a":100}

The following query returns all elements in a JSON array. The WITH CONDITIONAL WRAPPER clause is specified and the path expression matches multiple JSON objects. Therefore, the value returned is wrapped in an array.

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]'
       WITH CONDITIONAL WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[{"a":100},{"b":200},{"c":300}]

The following query is similar to the previous query, except that the value returned is of data type VARCHAR2(100).

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]'
       RETURNING VARCHAR2(100) WITH CONDITIONAL WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[{"a":100},{"b":200},{"c":300}]

The following query returns the fourth element in a JSON array. However, the supplied JSON array does not contain a fourth element, which results in an error. The EMPTY ON ERROR clause is specified. Therefore, the query returns an empty JSON array.

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[3]'
       EMPTY ON ERROR) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[]