JSON_EXISTS predicate

The JSON_EXISTS predicate determines whether JSON data contains a JSON value that can be located using the specified sql-json-path-expression.

The result of the JSON_EXISTS predicate is true if at least one value can be located in JSON-expression using the sql-json-path-expression. If sql-json-path-expression uses strict mode and an error occurs, the result of the predicate is determined by the ON ERROR clause. The result of the JSON_EXISTS predicate is unknown if JSON-expression is the null value.

JSON-expression

An expression that returns a built-in string data type. If it is a character or graphic data type, it must contain correctly formatted JSON data. If it is a binary data type, it is interpreted according to the explicit or implicit FORMAT clause.

FORMAT JSON or FORMAT BSON

Specifies how JSON-expression is to be interpreted.

FORMAT JSON: JSON-expression contains JSON data. If JSON-expression is binary data, the data is interpreted as UTF-8 or UTF-16. Binary data cannot be encoded using an EBCDIC CCSID.
FORMAT BSON: JSON-expression contains the BSON representation of JSON data. When FORMAT BSON is specified, JSON-expression must be a binary string data type.

If the FORMAT clause is not specified and JSON-expression is a character or graphic string, JSON-expression is treated as JSON. If JSON-expression is a binary string, JSON-expression is treated as BSON.

sql-json-path-expression

An expression that returns a value that is a built-in character or graphic string data type. The string is interpreted as an SQL/JSON path expression and is used to locate a JSON value within the JSON data specified by JSON-expression. For information on the content of an SQL/JSON path expression, see sql-json-path-expression.

If the value of sql-json-path-expression is the empty string, a string of all blanks, or the null value, no value can be located so the result of the predicate is false.

AS path-name

Specifies a name to be used to identify the sql-json-path-expression.

FALSE ON ERROR, TRUE ON ERROR, UNKNOWN ON ERROR, or ERROR ON ERROR

Specifies the result of the predicate when an error is encountered.

FALSE ON ERROR: The result is false if an error is encountered. This is the default.
TRUE ON ERROR: The result is true if an error is encountered.
UNKNOWN ON ERROR: The result is unknown if an error is encountered.
ERROR ON ERROR: An error is returned if an error is encountered.

Example

Return rows for employees who do not have an emergency contact in their JSON_DATA column. COALESCE causes null values to be treated as an empty string. The FALSE ON ERROR clause is used so all rows that do not contain an emergency value will be returned.
```
SELECT empno, lastname FROM employee 
  WHERE NOT JSON_EXISTS(COALESCE(json_data, ''), 'strict $.emergency' FALSE ON ERROR);
```