The JSON_EXISTS predicate determines whether JSON data contains a JSON value that can be located by 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 by using 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.
- An expression that returns a value that is a built-in string data type, except the following
data types (SQLSTATE 42815):
- CHAR FOR BIT DATA
- VARCHAR FOR BIT DATA
- A user-defined type that is sourced on any of the previously listed data types
If a character value is returned, it must contain correctly formatted JSON data (SQLSTATE 22032). If a binary data type is returned, it is interpreted according to the explicit or implicit FORMAT clause.
- FORMAT JSON
- JSON-expression is formatted as JSON data.
If JSON-expression is a character string data type, it is treated as JSON data.
If JSON-expression is a binary string data type, it is interpreted as UTF-8 data.
- FORMAT BSON
- Specifies that JSON-expression is formatted as the BSON representation of JSON data (SQLSTATE 22032). JSON-expression must be a binary string data type (SQLSTATE 42815).
- An expression that returns a value that is a built-in character 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 that is specified by JSON-expression. For more information about the SQL/JSON path expression, see sql-json-path-expression.
- AS path-name
- Specifies a name to be used to identify sql-json-path-expression.
- ON ERROR
- Specifies the behavior when an error is encountered by JSON_EXISTS.
- FALSE ON ERROR
- The result is false if an error is encountered. This clause 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.
- If parameter markers are not explicitly cast to a supported data type, an error is returned (SQLSTATE 42815)
- 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 are
SELECT empno, lastname FROM employee WHERE NOT JSON_EXISTS(COALESCE(JSON_DATA, ''), 'strict $.emergency' FALSE ON ERROR);