JSON_VALUE() Function in Oracle

by

In Oracle Database, the JSON_VALUE() function finds a specified scalar JSON value in JSON data and returns it as a SQL value. 

Syntax

The syntax goes like this:

JSON_VALUE
  ( expr [ FORMAT JSON ], JSON_basic_path_expression
    [ JSON_value_returning_clause ] [ JSON_value_on_error_clause ]
    [ JSON_value_on_empty_clause ][ JSON_value_on_mismatch_clause ]
  )

Where:

  • expr is the JSON document you want to query
  • JSON_basic_path_expression is the SQL/JSON path expression. This path expression is used to evaluate expr and find a scalar JSON value that matches, or satisfies, the path expression.
  • JSON_query_returning_clause specifies the data type and format of the value returned by the function.
  • 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.
  • JSON_value_on_mismatch_clause can be used to specify ignore, error, or null for error cases such as extra data, missing data, and type errors. It can be applied generally, or case by case.

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

Example

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

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

Result:

2

In this case I specified $.b to return the scalar value at b.

Here’s an example of returning a value from an array:

SELECT 
    JSON_VALUE('["a", "b", "c"]', '$[2]')
FROM DUAL;

Result:

c

Arrays are zero-based, and so $[2] returns the third array element.

Error Handling

There are three 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.
  • DEFAULT literal ON ERROR – Returns literal when an error occurs. The data type of literal must match the data type of the value returned by the function.

Here’s an example:

SELECT 
    JSON_VALUE('["a", "b", "c"]', '$[3]' NULL ON ERROR) AS "r1",
    JSON_VALUE('["a", "b", "c"]', '$[3]' DEFAULT 'Oops!' ON ERROR) AS "r2"
FROM DUAL;

Result:

     r1       r2 
_______ ________ 
null    Oops!

Here it is with the ERROR ON ERROR clause:

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

Result:

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

The above error clauses are for handling the following errors:

  • The first argument is not well-formed JSON data using strict or lax JSON syntax
  • A nonscalar value is found when the JSON data is evaluated using the SQL/JSON path expression
  • 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_value_on_empty_clause.
  • The return value data type is not large enough to hold the return value

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.
  • DEFAULT literal ON EMPTY – Returns literal when no match is found. The data type of literal must match the data type of the value returned by this function.

Example:

SELECT 
    JSON_VALUE('["a", "b", "c"]', '$[3]' DEFAULT 'Dang!' ON EMPTY)
FROM DUAL;

Result:

Dang!

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