Oracle JSON_QUERY() Function

Oracle JSON_QUERY() is a built-in function that is used to select and return one or more values from JSON data. You can use JSON_QUERY to retrieve fragments of a JSON document.

Oracle `JSON_QUERY()` Syntax

Here is the syntax for the Oracle JSON_QUERY() function:

JSON_QUERY
  ( expr [ FORMAT JSON ], path
    [ JSON_query_returning_clause ] [ JSON_query_wrapper_clause ]
    [ JSON_query_on_error_clause ] [ JSON_query_on_empty_clause ]
  )

Parameters

expr

Required. It specifies the JSON data to be queried. expr is an SQL expression that is an instance of one of the following data types: JSON, VARCHAR2, CLOB, or BLOB.

FORMAT JSON

Optional. If expr is a column of data type BLOB, FORMAT JSON must be specified.

path

Required. It specifies the SQL/JSON path expression. This function evaluates expr with the path expression and searches for scalar JSON values that match or satisfy the path expression. The path expression must be a text literal.

JSON_query_returning_clause

Optional. This clause specifies the data type and format of the value returned by this function. You can use RETURNING JSON_query_return_type, PRETTY, or ASCII. You can use the RETURNING clause to specify the data type of the returned instance, which is one of VARCHAR2, CLOB, or BLOB. You can specify PRETTY to beautify the printed character string returned by inserting line breaks and indentation. You can specify ASCII to automatically escape any non-ASCII Unicode characters in the returned character string using standard ASCII Unicode escape sequences.

JSON_query_wrapper_clause

Optional. You can use this clause to control whether this function wraps the values that match the path expression in an array wrapper, i.e., includes the value sequence in square brackets ([]).

Specify WITHOUT WRAPPER to omit the array wrapper. This clause can be specified only if the path expression matches a single JSON object or JSON array. This is the default value.
Specify WITH WRAPPER to include the array wrapper. This clause must be specified if the path expression matches a single scalar value (not a JSON object or JSON array) or multiple values of any type.
Specify 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 when the path expression matches a single scalar value or multiple values of any type. Omit the array wrapper if the path expression matches a single JSON object or JSON array.

JSON_query_on_error_clause

Optional. You can use this clause to specify the value returned by this function when the following errors occur. You can specify the following clauses:

NULL ON ERROR - Return null when an error occurs. This is the default value.
ERROR ON ERROR - Return the appropriate Oracle error when an error occurs.
EMPTY ON ERROR - Specify this clause is equivalent to specifying EMPTY ARRAY ON ERROR.
EMPTY ARRAY ON ERROR - Return an empty JSON array ([]) when an error occurs.
EMPTY OBJECT ON ERROR - Return an empty JSON object ({}) when an error occurs.

JSON_query_on_empty_clause

Optional. You can use this clause to specify the value returned by the function when no matches are found while calculating JSON data with SQL/JSON path expression. You can specify the following clauses:

NULL ON EMPTY - Returns null when no matches are found.
ERROR ON EMPTY - Returns appropriate Oracle error when no matches are found.
EMPTY ON EMPTY - Specifies that this clause is equivalent to EMPTY ARRAY ON EMPTY.
EMPTY ARRAY ON EMPTY - Returns an empty JSON array ([]) when no matches are found.
EMPTY OBJECT ON EMPTY - Returns an empty JSON object ({}) when no matches are found.

Return value

The Oracle JSON_QUERY() function is used to select and return one or more values from JSON data.

Oracle `JSON_QUERY()` Examples

Here are some examples that demonstrate the usage of the Oracle JSON_QUERY() function.

Example 1

The following query returns the context item or the specified JSON data string.

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

输出：

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

Example 2

The following query returns the value of the member with the property name a. The path expression matches a scalar value and 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]

Example 3

The following query returns the values of all object members. The path expression matches multiple values, which must be enclosed together 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]

Example 4

The following query returns the context item or the specified JSON data string. The path expression matches a single JSON array, and an array wrapper is not required.

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

输出：

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

Example 5

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

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

输出：

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

Example 6

The following query returns all elements in the JSON array. The path expression matches multiple values, which must be enclosed together 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]

Example 7

The following query returns elements with indexes 0, 3 to 5, and 7 from a JSON array. The path expression matches multiple values that must be included together in an array wrapper. Therefore, the WITH WRAPPER clause is used.

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]

Example 8

The following query returns the fourth element from a JSON array. The path expression matches a scalar value that must be included in an array wrapper. Therefore, the WITH WRAPPER clause is used.

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

输出：

VALUE
________
[3]

Example 9

The following query returns the first element from a JSON array. The WITH CONDITIONAL WRAPPER clause is used, and the path expression matches a single JSON object. Therefore, the returned value is not included in an array. Note that the JSON data in the returned value is converted to strict JSON syntax, i.e., object property names are enclosed in double quotes.

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

输出：

VALUE
____________
{"a":100}

Example 10

The following query returns all elements from a JSON array. The WITH CONDITIONAL WRAPPER clause is used, and the path expression matches multiple JSON objects. Therefore, the returned value is included 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}]

Example 11

The following query is similar to the previous query, except that the data type of the returned value is 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}]

Example 12

The following query returns the fourth element from a JSON array. However, the provided JSON array does not include the fourth element, which results in an error. The EMPTY ON ERROR clause is used. 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
________
[]

Conclusion

Oracle JSON_QUERY() is a built-in function that selects and returns one or more values from JSON data. You can use JSON_QUERY to retrieve fragments of a JSON document.

Oracle JSON_QUERY() Syntax