Oracle JSON_VALUE() Function

Oracle JSON_VALUE() is a built-in function that searches for a specified scalar JSON value in JSON data and returns it as an SQL value.

Oracle `JSON_VALUE()` Syntax

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

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

Parameters

expr

Required. It is the JSON document to query from. Specify an expression that evaluates to a text literal for expr. If expr is a column, the column must be of data type VARCHAR2, CLOB, or BLOB. If expr is empty, the function returns null.

FORMAT JSON

Optional. Required if expr is a column of data type BLOB.

path

Required. It specifies a SQL/JSON path expression. The 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_value_returning_clause

Optional. This clause specifies the data type and format of the value returned by this function. You can use either RETURNING JSON_value_return_type or ASCII.

JSON_value_on_error_clause

Optional. You can specify one of 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.
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 this function.

JSON_value_on_empty_clause

Optional. You can specify one of the following clauses:

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.

JSON_value_on_mismatch_clause

Optional. It can be used to specify ignore, error, or null for error cases such as extra data, missing data, and type errors. It can be generally applied or applied on a case-by-case basis.

Return Value

The Oracle JSON_VALUE() function searches for data from the specified JSON document based on the specified path and returns the result of the search.

If any argument is NULL, JSON_VALUE() returns NULL.

Oracle `JSON_VALUE()` Examples

Here are some examples that demonstrate how to use the Oracle JSON_VALUE() function.

JSON Array

The following example demonstrates how to use the Oracle JSON_VALUE() function to retrieve the second element from a JSON array:

SELECT JSON_VALUE('[1,2,3]', '$[1]') Result
FROM dual;

输出：

RESULT
_________
2

In this example, the $[1] path expression represents the second element in the JSON array.

JSON Object

The following example demonstrates how to use the Oracle JSON_VALUE() function to retrieve a member of a JSON object:

SELECT JSON_VALUE('{"x":1, "y":2}', '$.y') Result
FROM dual;

输出：

RESULT
_________
2

In this example, the $.y path expression represents the member y of a JSON object.

Error Handling

You can specify how errors are handled using the following clauses:

NULL ON ERROR - Returns a null value when an error occurs. This is the default value.
ERROR ON ERROR - Returns the corresponding Oracle error when an error occurs.
DEFAULT value - Returns value when an error occurs. The data type of value must match the data type of the value returned by the function.

The following statement shows how to return NULL when a path with an error is specified:

SET NULL 'NULL';
SELECT
    JSON_VALUE('[1,2]', '$[2]') Result1,
    JSON_VALUE('[1,2]', '$[2]' NULL ON ERROR) Result2
FROM DUAL;

输出：

RESULT1    RESULT2
__________ __________
NULL       NULL

The following statement shows how to report an error message when a path with an error is specified:

SELECT JSON_VALUE('[1,2]', '$[2]' ERROR ON ERROR)
FROM DUAL;

输出：

SQL Error: ORA-40462: JSON_VALUE evaluated to no value
40462. 00000 -  "JSON_VALUE evaluated to no value"
*Cause:    The provided JavaScript Object Notation (JSON) path expression
           did not select a value.
*Action:   Correct the JSON path expression.

The following statement shows how to return a default value of 0 when a path with an error is specified:

SELECT
    JSON_VALUE('[1,2]', '$[2]' DEFAULT '0' ON ERROR) Result
FROM DUAL;

输出：

RESULT
_________
0

Conclusion

Oracle JSON_VALUE() is a built-in function that looks for a specified scalar JSON value in JSON data and returns it as an SQL value.

Oracle JSON_VALUE() Syntax