Purpose
The JSON_VALUE() function is used to query a specific part of JSON data and return an 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
JSON_VALUE() function syntax is described as follows:
[RETURNING NUMBER | DATE | TIMESTAMP | VARCHAR2 | VARCHAR2[size]: Optional. Specifies the return value type.VARCHAR2[size]can be used to limit the length of the return value. The default isVARCHAR2(4000).FORMAT JSON: Optional. This clause 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 processed result.EMPTY: Specifies the processed result when a null value is encountered during the search.MISMATCH: Controls the output of type conversion errors.
Examples
# Use default parameters
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