Purpose
JSON_EXISTS() works as an SQL condition in an SQL statement to check whether the JSON data in a specified path exists or meets specific conditions. This function can be understood as a filter condition for JSON data. If the JSON data exists, this function returns TRUE. If the JSON data does not exist, this function 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]);
Parameters
The parameters in the syntax are described as follows:
FORMAT JSON: This clause is optional. It is required when the data type ofexprisBLOB.json_path_literal: specifies the query path. This parameter is required. You can use ajson_pathstatement with a filter condition.ERROR:ERROR ON ERROR: specifies to return an error when the value ofexpris not valid JSON data.TRUE ON ERROR: specifies to returnTRUEwhen the value ofexpris not valid JSON data.FALSE ON ERROR: specifies to returnFALSEwhen the value ofexpris not valid JSON data. This is the default setting.
EMPTY: specifies to returnFALSEwhen noTRUEresult exists.PASSING: specifies to pass external SQL variables to thejson_pathparameter for comparing the values in the path specified in the filter expression with the SQL variables.
Examples
# Use default arguments to select JSON values meeting 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
# Set the JSON_EXIST clause to return FALSE when no matching item is found or the value of 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
# Set the PASSING clause to pass variables 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