Purpose
JSON_QUERY() finds one or more specified JSON values in JSON data and returns the values in a character string.
Syntax
JSON_QUERY(
expr [FORMAT JSON],
json_path_literal
[RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size],]
[ALLOW|DISALLOW SCALARS]
[PRETTY]
[ASCII]
[WITHOUT [ARRAY] WRAPPER, WITH CONDITIONAL|UNCONDITIONAL [ARRAY] WRAPPER]
[ERROR|NULL|EMPTY|EMTPY ARRAY|EMPTY OBJECT ON ERROR]
[ERROR|NULL|EMPTY|EMTPY ARRAY|EMPTY OBJECT ON EMPTY]);
Parameters
The parameters in the syntax are described as follows:
[RETURNING CLOB | BLOB | JSON | VARCHAR2 | VARCHAR2[size]: specifies the return value type.VARCHAR2[size]specifies the length of the return value. The default return value type depends on the input data type. If the input data type is JSON, the default return value type is JSON. If the input data type isVARCHAR, the default return value type isVARCHAR.FORMAT JSON: This clause is optional. It is required when the data type ofexprisBLOB.json_path_literal: specifies the query path.PRETTY: specifies whether to use pretty-print for returning character data types.ASCII: specifies to automatically escape any non-ASCII Unicode characters in the returned string by using standard ASCII Unicode escape sequences.WITHOUT WRAPPER: specifies to ignore theARRAY WRAPPERfeature.WITH WRAPPER: specifies to use theARRAY WRAPPERfeature.WITH UNCONDITIONAL WRAPPER: This clause is equivalent toWITH WRAPPER.WITH CONDITIONAL WRAPPER: specifies to use theARRAY WRAPPERfeature for returning multiple values and ignore theARRAY WRAPPERfeature for returning a single value.ERROR:NULL ON ERROR: specifies to returnNULLwhen an error occurs. This is the default setting.ERROR ON ERROR: specifies to return the corresponding Oracle error message when an error occurs.EMPTY ON ERROR: This clause is equivalent toEMPTY ARRAY ON ERROR.EMPTY ARRAY ON ERROR: specifies to return an empty JSON array when an error occurs.EMPTY OBJECT ON ERROR: specifies to return an empty JSON object when an error occurs.
EMPTY:NULL ON EMPTY: specifies to returnNULLwhen no matching item is found. This is the default setting.ERROR ON EMPTY: specifies to return the corresponding Oracle error message when no matching item is found.EMPTY ON EMPTY: This clause is equivalent toEMPTY ARRAY ON EMPTY.EMPTY ARRAY ON EMPTY: specifies to return an empty JSON array when no matching item is found.EMPTY OBJECT ON EMPTY: specifies to return an empty JSON object when no matching item is found.
Examples
# Use the RETURNING clause.
obclient> SELECT JSON_QUERY('{"a":100, "b":200, "c":300}', '$') AS value FROM DUAL;
+--------------------------------+
| VALUE |
+--------------------------------+
| {"a": 100, "b": 200, "c": 300} |
+--------------------------------+
1 row in set
obclient>SELECT JSON_QUERY('{"a":100, "b":200, "c":300}', '$' RETURNING CLOB) FROM DUAL;
+----------------------------------------------------------+
| JSON_QUERY('{"A":100,"B":200,"C":300}','$'RETURNINGCLOB) |
+----------------------------------------------------------+
| {"a": 100, "b": 200, "c": 300} |
+----------------------------------------------------------+
1 row in set
obclient> SELECT JSON_QUERY('{"a":100, "b":200, "c":300}', '$' RETURNING VARCHAR2(200)) FROM DUAL;
+-------------------------------------------------------------------+
| JSON_QUERY('{"A":100,"B":200,"C":300}','$'RETURNINGVARCHAR2(200)) |
+-------------------------------------------------------------------+
| {"a": 100, "b": 200, "c": 300} |
+-------------------------------------------------------------------+
1 row in set
obclient> SELECT JSON_QUERY('{"a":100, "b":200, "c":300}', '$' RETURNING BLOB) FROM DUAL;
+----------------------------------------------------------+
| JSON_QUERY('{"A":100,"B":200,"C":300}','$'RETURNINGBLOB) |
+----------------------------------------------------------+
| {"a": 100, "b": 200, "c": 300} |
+----------------------------------------------------------+
1 row in set
obclient> SELECT JSON_QUERY('{"a":100, "b":200, "c":300}', '$' RETURNING JSON) FROM DUAL;
+----------------------------------------------------------+
| JSON_QUERY('{"A":100,"B":200,"C":300}','$'RETURNINGJSON) |
+----------------------------------------------------------+
| {"a": 100, "b": 200, "c": 300} |
+----------------------------------------------------------+
1 row in set
# Use the WRAPPER and ERROR clauses.
obclient> SET @col_json = '[1,2]';
obclient> SELECT JSON_QUERY(@col_json, '$[0 to 10]' RETURNING JSON WITH WRAPPER EMPTY ON ERROR) FROM DUAL;
+----------------------------------------------------------------------+
| JSON_QUERY(@COL_JSON,'$[0TO10]'RETURNINGJSONWITHWRAPPEREMPTYONERROR) |
+----------------------------------------------------------------------+
| [1, 2] |
+----------------------------------------------------------------------+
1 row in set
# Use the ARRAY WRAPPER clause.
obclient> SELECT JSON_QUERY(treat('{"a":100, "b":200, "c":300}' as json), '$' WITH ARRAY WRAPPER) AS value FROM DUAL;
+----------------------------------+
| VALUE |
+----------------------------------+
| [{"a": 100, "b": 200, "c": 300}] |
+----------------------------------+
1 row in set
# Use the DISALLOW SCALAR clause.
obclient> SELECT JSON_QUERY('"aaa"', '$' DISALLOW SCALARS ) FROM DUAL;
+----------------------------------------+
| JSON_QUERY('"AAA"','$'DISALLOWSCALARS) |
+----------------------------------------+
| NULL |
+----------------------------------------+
1 row in set
# Use the EMPTY OBJECT/EMPTY ARRAY ON EMTPY clause.
obclient>SELECT JSON_QUERY('{}', '$.a' EMPTY OBJECT ON EMPTY) AS value FROM DUAL;
+-------+
| VALUE |
+-------+
| {} |
+-------+
1 row in set
obclient>SELECT JSON_QUERY('{}', '$.a' EMPTY ARRAY ON EMPTY) AS value FROM DUAL;
+-------+
| VALUE |
+-------+
| [] |
+-------+
1 row in set