JSON_EXISTS Condition

Use the SQL/JSON condition JSON_EXISTS to test whether a specified JSON value exists in JSON data. This condition returns TRUE if the JSON value exists and FALSE if the JSON value does not exist.

JSON_exists_condition::=

Description of the illustration json_exists_condition.eps

(JSON_basic_path_expression: See Oracle AI Database JSON Developer’s Guide)

JSON_passing_clause::=

Description of the illustration json_passing_clause.eps

JSON_exists_on_error_clause::=

Description of the illustration json_exists_on_error_clause.eps

JSON_exists_on_empty_clause::=

Description of the illustration json_exists_on_empty_clause.eps

expr

Use this clause to specify the JSON data to be evaluated. For expr, specify an expression that evaluates to a text literal. If expr is a column, then the column must be of data type VARCHAR2, CLOB, or BLOB. If expr evaluates to null or a text literal of length zero, then the condition returns UNKNOWN.

If expr is not a text literal of well-formed JSON data using strict or lax syntax, then the condition returns FALSE by default. You can use the JSON_exists_on_error_clause to override this default behavior. Refer to the JSON_exists_on_error_clause.

FORMAT JSON

You must specify FORMAT JSON if expr is a column of data type BLOB.

JSON_basic_path_expression

Use this clause to specify a SQL/JSON path expression. The condition uses the path expression to evaluate expr and determine if a JSON value that matches, or satisfies, the path expression exists. The path expression must be a text literal, but it can contain variables whose values are passed to the path expression by the JSON_passing_clause. See Oracle AI Database JSON Developer’s Guide for the full semantics of JSON_basic_path_expression.

JSON_passing_clause

Use this clause to pass values to the path expression. For expr, specify a value of data type VARCHAR2, NUMBER, BINARY_DOUBLE, DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE. The result of evaluating expr is bound to the corresponding identifier in the JSON_basic_path_expression.

JSON_exists_on_error_clause

Use this clause to specify the value returned by this condition when expr is not well-formed JSON data.

You can specify the following clauses:

ERROR ON ERROR - Returns the appropriate Oracle error when expr is not well-formed JSON data.
TRUE ON ERROR - Returns TRUE when expr is not well-formed JSON data.
FALSE ON ERROR - Returns FALSE when expr is not well-formed JSON data. This is the default.

TYPE Clause

For a full discussion of STRICT and LAX syntax see About Strict and Lax JSON Syntax, and TYPE Clause for SQL Functions and Conditions

JSON_exists_on_empty_clause

Use this clause to specify the value returned by this function if no match is found when the JSON data is evaluated using the SQL/JSON path expression.

You can specify the following clauses:

ERROR ON EMPTY - Returns the appropriate Oracle error when expr is not well-formed JSON data.
TRUE ON EMPTY - Returns TRUE when expr is not well-formed JSON data.
FALSE ON EMPTY - Returns FALSE when expr is not well-formed JSON data. This is the default.

Examples

The following statement creates table t with column name:

CREATE TABLE t (name VARCHAR2(100));

The following statements insert values into column name of table t:

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');

The following statement queries column name in table t and returns JSON data that consists of an array whose first element is an object with property name first. The ON ERROR clause is not specified. Therefore, the JSON_EXISTS condition returns FALSE for values that are not well-formed JSON data.

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"}]

The following statement queries column name in table t and returns JSON data that consists of an array whose second element is an object with property name middle. The ON ERROR clause is not specified. Therefore, the JSON_EXISTS condition returns FALSE for values that are not well-formed JSON data.

SELECT name FROM t
  WHERE JSON_EXISTS(name, '$[1].middle');

NAME
--------------------------------------------------------------------------------
[{first:"John"}, {middle:"Mark"}, {last:"Smith"}]
[{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]

The following statement is similar to the previous statement, except that the TRUE ON ERROR clause is specified. Therefore, the JSON_EXISTS condition returns TRUE for values that are not well-formed JSON data.

SELECT name FROM t
  WHERE JSON_EXISTS(name, '$[1].middle' TRUE ON ERROR);

NAME
--------------------------------------------------------------------------------
[{first:"John"}, {middle:"Mark"}, {last:"Smith"}]
[{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]
This is not well-formed JSON data

The following statement queries column name in table t and returns JSON data that consists of an array that contains an element that is an object with property name last. The wildcard symbol (*) is specified for the array index. Therefore, the query returns arrays that contain such an object, regardless of its index number in the array.

SELECT name FROM t
  WHERE JSON_EXISTS(name, '$[*].last');

NAME
--------------------------------------------------
[{first:"John"}, {middle:"Mark"}, {last:"Smith"}]
[{first:"Mary"}, {last:"Jones"}]
[{first:"Jeff"}, {last:"Williams"}]
[{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]

The following statement performs a filter expression using the passing clause. The SQL/JSON variable $var1 in the comparison predicate (@.middle == $var1) gets its value from the bind variable var1 of the PASSING clause.

Using bind variables for value comparisons avoids query re-compilation.

SELECT name FROM t

  WHERE JSON_EXISTS(name, '$[1]?(@.middle == $var1)' PASSING 'Anne' as "var1");

NAME

--------------------------------------------------------------------------------

[{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]