Purpose
The JSON_QUERY() function is used to query one or more values from JSON data and returns these values.
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]);
Syntax
The syntax of the JSON_QUERY() function is as follows:
[RETURNING CLOB | BLOB | JSON | VARCHAR2 | VARCHAR2[size]: Specifies the return value type.VARCHAR2[size]can be used to limit the length of the returned value. The default return type depends on the input data type. If the input is JSON, the output type is also JSON. If the input type isVARCHAR, the default output type isVARCHAR.FORMAT JSON: Optional. This clause is required when the data type ofexprisBLOB.json_path_literal: Specifies the query path.PRETTY: Specifies whether to use pretty-print when returning a character type.ASCII: Automatically escapes any non-ASCII Unicode characters in the returned string using standard ASCII Unicode escape sequences.WITHOUT WRAPPER: Disables theARRAY WRAPPERfeature.WITH WRAPPER: Enables theARRAY WRAPPERfeature.WITH UNCONDITIONAL WRAPPER: Equivalent toWITH WRAPPER.WITH CONDITIONAL WRAPPER: Uses theARRAY WRAPPERfeature when returning multiple values. If a single value is returned, theARRAY WRAPPERfeature is ignored.ERRORclause:NULL ON ERROR: ReturnsNULLwhen an error occurs. This is the default setting.ERROR ON ERROR: Returns the corresponding Oracle error when an error occurs.EMPTY ON ERROR: Equivalent to specifyingEMPTY ARRAY ON ERROR.EMPTY ARRAY ON ERROR: Returns an empty JSON array when an error occurs.EMPTY OBJECT ON ERROR: Returns an empty JSON object when an error occurs.
EMPTYclause:NULL ON EMPTY: ReturnsNULLwhen no match is found. This is the default setting.ERROR ON EMPTY: Returns the corresponding Oracle error when no match is found.EMPTY ON EMPTY: Equivalent to specifyingEMPTY ARRAY ON EMPTY.EMPTY ARRAY ON EMPTY: Returns an empty JSON array when no match is found.EMPTY OBJECT ON EMPTY: Returns an empty JSON object when no match 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 EMPTY 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