JSON_EXISTS predicate

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

Read syntax diagramSkip visual syntax diagramJSON_EXISTS(JSON-expression FORMAT JSONFORMAT BSON,sql-json-path-expression ASpath-nameFALSE ON ERRORTRUEUNKNOWNERRORON ERROR)

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.

JSON-expression
An expression that returns a value that is a built-in string data type, except the following data types (SQLSTATE 42815):
  • GRAPHIC
  • VARGRAPHIC
  • DBCLOB
  • BINARY
  • 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).
sql-json-path-expression
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.

Notes

  • If parameter markers are not explicitly cast to a supported data type, an error is returned (SQLSTATE 42815)

Example

  1. 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 returned.
    SELECT empno, lastname FROM employee
  WHERE NOT JSON_EXISTS(COALESCE(JSON_DATA, ''), 'strict $.emergency' FALSE ON ERROR);