Purpose
The JSON_EXISTS() function is used in SQL statements as a condition to check whether JSON data exists at a specified path or meets certain conditions. It acts as a filter for JSON data. If the JSON value exists, the condition returns TRUE; otherwise, it returns FALSE.
Syntax
JSON_EXISTS(
expr [FORMAT JSON],
json_path_literal
[PASSING expr AS identifier]
[ERROR|TRUE|FALSE ON ERROR]
[ERROR|TRUE|FALSE ON EMPTY]);
Syntax
The syntax of the JSON_EXISTS() function is as follows:
FORMAT JSONclause: Optional. Required when the data type ofexprisBLOB.json_path_literal: Required. Specifies the path for the query. You can use ajson_pathstatement with a filter condition here.ERRORclause:ERROR ON ERROR: Returns an error whenexpris not well-formed JSON data.TRUE ON ERROR: ReturnsTRUEwhenexpris not well-formed JSON data.FALSE ON ERROR: ReturnsFALSEwhenexpris not well-formed JSON data. This is the default option.
EMPTYclause: ReturnsFALSEif there are no results that areTRUE.PASSINGclause: Passes external SQL variables tojson_pathfor comparison with values at the path specified in the filter expression.
Examples
# Use default parameters to select JSON values that meet the conditions.
CREATE TABLE t (name VARCHAR2(100));
INSERT INTO t VALUES ('[{first:"John"}, {middle:"Mark"}, {last:"Smith"}]');
INSERT INTO t VALUES ('[{first:"Mary"}, {last:"Jones"}]');
INSERT INTO t VALUES ('[{first:"Jeff"}, {last:"Williams"}]');
INSERT INTO t VALUES ('[{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]');
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES ('This is not well-formed JSON data');
obclient> SELECT name FROM t WHERE JSON_EXISTS(name, '$[0].first');
+---------------------------------------------------+
| NAME |
+---------------------------------------------------+
| [{first:"John"}, {middle:"Mark"}, {last:"Smith"}] |
| [{first:"Mary"}, {last:"Jones"}] |
| [{first:"Jeff"}, {last:"Williams"}] |
| [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}] |
+---------------------------------------------------+
4 rows in set
# JSON_EXISTS returns false when there is no match or when expr is not valid JSON data.
obclient> SELECT name FROM t WHERE JSON_EXISTS(name, '$[1].middle');
+---------------------------------------------------+
| NAME |
+---------------------------------------------------+
| [{first:"John"}, {middle:"Mark"}, {last:"Smith"}] |
| [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}] |
+---------------------------------------------------+
2 rows in set
# Use the PASSING clause to pass parameters to the json-path parameter.
obclient> SELECT name FROM t WHERE JSON_EXISTS(name, '$[1]?(@.middle == $var1)' PASSING 'Anne' as "var1");
+---------------------------------------------------+
| NAME |
+---------------------------------------------------+
| [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}] |
+---------------------------------------------------+
1 row in set