JSON_QUERY() Function in Oracle

by

In Oracle Database, the JSON_QUERY() function selects and returns one or more values from JSON data and returns those values. 

You can use the function to retrieve fragments of a JSON document.

Syntax

The syntax goes like this:

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

Where:

  • expr is the JSON document you want to query
  • JSON_basic_path_expression is the SQL/JSON path you want to return from that JSON document. 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.
  • JSON_query_returning_clause specifies the data type and format of the character string returned by the function.
  • JSON_query_wrapper_clause controls whether the function wraps the returned values in an array wrapper (square brackets ([])).
  • JSON_query_on_error_clause specifies the value returned when certain errors occur.
  • JSON_query_on_empty_clause specifies the value returned if no match is found when the JSON data is evaluated using the SQL/JSON path expression.

See the Oracle documentation for a detailed explanation of the optional clauses.

Example

Here’s an example to demonstrate how the JSON_QUERY() function works:

SELECT 
    JSON_QUERY('{a:1, b:2, c:3}', '$')
FROM DUAL;

Result:

{"a":1,"b":2,"c":3}

Using the dollar sign by itself returns the whole string of JSON data.

With Wrapper

You can use the WITH WRAPPER clause to wrap the result in an array wrapper:

SELECT 
    JSON_QUERY('{a:1, b:2, c:3}', '$' WITH WRAPPER)
FROM DUAL;

Result:

[{"a":1,"b":2,"c":3}]

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.

For example, returning the following scalar value must be done with the WITH WRAPPER clause:

SELECT 
    JSON_QUERY('{a:1, b:2, c:3}', '$.b' WITH WRAPPER)
FROM DUAL;

Result:

[2]

Omitting the WITH WRAPPER clause when returning a scalar value results in a null value being returned:

SET NULL 'null';
SELECT 
    JSON_QUERY('{a:1, b:2, c:3}', '$.b')
FROM DUAL;

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

With Conditional Wrapper

Alternatively, you can use the WITH CONDITIONAL WRAPPER clause 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:

SELECT 
    JSON_QUERY('{a:1, b:2, c:3}', '$' WITH CONDITIONAL WRAPPER) AS "r1",
    JSON_QUERY('{a:1, b:2, c:3}', '$.b' WITH CONDITIONAL WRAPPER) AS "r2"
FROM DUAL;

Result:

                    r1     r2 
______________________ ______ 
{"a":1,"b":2,"c":3}    [2]

In this case, only the scalar value has the array wrapper applied.

Just to be clear, here it is with an unconditional wrapper:

SELECT 
    JSON_QUERY('{a:1, b:2, c:3}', '$' WITH UNCONDITIONAL WRAPPER) AS "r1",
    JSON_QUERY('{a:1, b:2, c:3}', '$.b' WITH UNCONDITIONAL WRAPPER) AS "r2"
FROM DUAL;

Result:

                      r1     r2 
________________________ ______ 
[{"a":1,"b":2,"c":3}]    [2]

This time both results have the wrapper applied.

Note that WITH UNCONDITIONAL WRAPPER is the equivalent of WITH WRAPPER.

Error Handling

There are five clauses that you can use to specify what to return whenever certain kinds of errors occur. The clauses are:

  • 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 – Specifying this clause is equivalent to specifying EMPTY ARRAY ON ERROR.
  • EMPTY ARRAY ON ERROR – Returns an empty JSON array ([]) when an error occurs.
  • EMPTY OBJECT ON ERROR – Returns an empty JSON object ({}) when an error occurs.

Here’s an example:

SELECT 
    JSON_QUERY('["a", "b", "c"]', '$[3]' NULL ON ERROR) AS "r1",
    JSON_QUERY('["a", "b", "c"]', '$[3]' EMPTY ON ERROR) AS "r2",
    JSON_QUERY('["a", "b", "c"]', '$[3]' EMPTY OBJECT ON ERROR) AS "r3"
FROM DUAL;

Result:

     r1    r2    r3 
_______ _____ _____ 
null    []    {}

Here it is with the ERROR ON ERROR clause:

SELECT 
    JSON_QUERY('["a", "b", "c"]', '$[3]' ERROR ON ERROR)
FROM DUAL;

Result:

Error report -
ORA-40462: JSON_VALUE evaluated to no value

These error clauses are for handling the following errors:

  • The first argument is not well-formed JSON data using strict or lax JSON syntax
  • No match is found when the JSON data is evaluated using the SQL/JSON path expression. You can override the behaviour for this type of error by specifying the JSON_query_on_empty_clause.
  • 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 a wrapper clause is not specified

Handling Empty Results

You can also use a clause to specify the value returned if no match is found. This clause allows you to specify a different outcome for this type of error than the outcome specified with the error clauses.

These clauses are:

  • NULL ON EMPTY – Returns null when no match is found.
  • ERROR ON EMPTY – Returns the appropriate Oracle error when no match is found.
  • EMPTY ON EMPTY – Specifying this clause is equivalent to specifying EMPTY ARRAY ON EMPTY.
  • EMPTY ARRAY ON EMPTY – Returns an empty JSON array ([]) when no match is found.
  • EMPTY OBJECT ON EMPTY – Returns an empty JSON object ({}) when no match is found.

Example:

SELECT 
    JSON_QUERY('["a", "b", "c"]', '$[3]' EMPTY ON EMPTY)
FROM DUAL;

Result:

[]

If you omit this clause, then the error clause determines the value returned when no match is found.