Purpose
The JSON_EXISTS() function is used as a SQL condition in an SQL statement 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; if the JSON value does not exist, 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. This clause must be used when the data type ofexprisBLOB.json_path_literal: Required. This 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 valid JSON data.TRUE ON ERROR: ReturnsTRUEwhenexpris not valid JSON data.FALSE ON ERROR: ReturnsFALSEwhenexpris not valid JSON data. This is the default option.
EMPTYclause: ReturnsFALSEif no result isTRUE.PASSINGclause: Passes external SQL variables tojson_pathfor comparison with values specified by the Path 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
# When a match is not found or expr is not valid JSON data, JSON_EXIST returns false.
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 a value 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