IS JSON predicate
The IS JSON predicate determines whether a value is in a JSON format with the specified attributes.
The result of the IS JSON predicate is true if the JSON-expression conforms to the JSON format specified by the item type and the UNIQUE KEYS clause. The result is false if the JSON-expression does not conform to the JSON format specified by the item type and the UNIQUE KEYS clause or if JSON-expression is the empty string. If NOT is specified, the result is reversed. If the value of JSON-expression is null, the result is unknown.
- JSON-expression
- An expression that returns a value that is a built-in string data type.
- 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.
- VALUE, ARRAY, OBJECT, or SCALAR
- Specifies the type of JSON item to compare with the contents of JSON-expression.
- VALUE
- A valid JSON value of any of the types ARRAY, OBJECT, or SCALAR.
- ARRAY
- A valid JSON array which is a list of values separated by commas and enclosed in square brackets.
- OBJECT
- A valid JSON object which is a list of key:value pairs separated by commas and enclosed between a left brace and right brace.
- SCALAR
- A valid JSON value that is not a JSON array or a JSON object. A scalar value can be a character string, a number, or one of the JSON literals: null, true, or false.
- WITHOUT UNIQUE KEYS or WITH UNIQUE KEYS
- Specifies whether JSON-expression is considered to be valid JSON when keys are not
unique. This clause is ignored if the JSON item is not a JSON object.
- WITHOUT UNIQUE KEYS
- A JSON object with keys that are not unique is valid JSON.
- WITH UNIQUE KEYS
- A JSON object with keys that are not unique is not valid JSON.
Example
- Create a trigger to ensure only a valid JSON object can be inserted
into a JSON_DATA column.
CREATE TRIGGER VALIDATE_JSON BEFORE INSERT ON T REFERENCING NEW AS N FOR EACH ROW IF N.JSON_DATA IS NOT JSON OBJECT THEN SIGNAL SQLSTATE '75007' SET MESSAGE_TEXT = 'Input is not valid JSON'); END IF