Purpose
JSON_VALUE() finds a specified scalar JSON value in JSON data and returns it as a SQL value
Syntax
JSON_VALUE(value_expr [FORMAT JSON],
json_path_literal
[RETURNING NUMBER|DATE|TIMESTAMP|VARCHAR2|VARCHAR2[size],]
[ERROR|NULL|DEFAULT-literal ON ERROR]
[ERROR|NULL|DEFAULT-literal ON EMPTY]
[ERROR|NULL ON MISMATCH]);
Parameters
The parameters in the syntax are described as follows:
[RETURNING NUMBER | DATE | TIMESTAMP | VARCHAR2 | VARCHAR2[size]: specifies the return value type.VARCHAR2[size]specifies the length of the return value. The default return value type isVARCHAR2(4000). This clause is optional.FORMAT JSON: This clause is optional. It is required when the data type ofvalue_exprisBLOB.ERROR: controls the output of errors other than type conversion errors during expression execution and returns the processing result.EMPTY: returns the processing result when NULL values are found during lookup.MISMATCH: controls the output of type conversion errors.
Examples
# Use default arguments.
obclient> SELECT JSON_VALUE('{"a":100}', '$.a') AS value FROM DUAL;
+-------+
| VALUE |
+-------+
| 100 |
+-------+
1 row in set
# Use the ERROR clause.
obclient> SELECT JSON_VALUE( '{a:"cat"}','$.a.number()' NULL ON EMPTY DEFAULT -1 ON ERROR ) FROM DUAL;
+-------------------------------------------------------------------+
| JSON_VALUE('{A:"CAT"}','$.A.NUMBER()'NULLONEMPTYDEFAULT-1ONERROR) |
+-------------------------------------------------------------------+
| -1 |
+-------------------------------------------------------------------+
1 row in set
# Use the DEFAULT clause.
obclient> SELECT JSON_VALUE('{firstname:"John"}', '$.lastname' DEFAULT 'No last name found' ON ERROR) AS "Last Name" FROM DUAL;
+--------------------+
| Last Name |
+--------------------+
| No last name found |
+--------------------+
1 row in set
# Use the RETURNING clause.
obclient> SELECT JSON_VALUE('"2019-08-30, 1"', '$' RETURNING CLOB error on error) FROM DUAL;
+-----------------------------------------------------------+
| JSON_VALUE('"2019-08-30,1"','$'RETURNINGCLOBERRORONERROR) |
+-----------------------------------------------------------+
| 2019-08-30, 1 |
+-----------------------------------------------------------+
1 row in set