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