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.

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

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