Menu

MariaDB IS JSON Predicate: Validate JSON Types and Keys

MariaDB’s IS JSON predicate checks whether a string expression contains valid JSON. Optional type constraints restrict the top-level value, and WITH UNIQUE KEYS rejects duplicate object keys. The predicate was added in MariaDB 12.3. See the official IS JSON reference and MariaDB 12.3 changes.

Syntax

expression IS [NOT] JSON
    [VALUE | ARRAY | OBJECT | SCALAR]
    [[WITH | WITHOUT] UNIQUE [KEYS]]

If no type is specified, VALUE is assumed and any valid JSON value is accepted. WITH UNIQUE KEYS requires object member names to be unique; the default accepts duplicate keys.

Check validity and top-level type

SELECT
    '{"a": 1}' IS JSON OBJECT AS is_object,
    '[1, 2]' IS JSON ARRAY AS is_array,
    'invalid' IS JSON AS is_valid_json,
    NULL IS JSON AS null_input;

+-----------+----------+----------------+------------+
| is_object | is_array | is_valid_json  | null_input |
+-----------+----------+----------------+------------+
|         1 |        1 |              0 | NULL       |
+-----------+----------+----------------+------------+

Invalid JSON returns 0 instead of raising a parse error. A SQL NULL expression returns NULL.

Require unique object keys

Without WITH UNIQUE KEYS, an object with duplicate keys is valid JSON for this predicate. Add the option to reject duplicates:

SELECT
    '{"a": 1, "a": 2}' IS JSON AS accepts_duplicate_keys,
    '{"a": 1, "a": 2}' IS JSON WITH UNIQUE KEYS AS has_unique_keys;

+------------------------+----------------+
| accepts_duplicate_keys | has_unique_keys |
+------------------------+----------------+
|                      1 |              0 |
+------------------------+----------------+

For a function form that checks JSON syntax only, see JSON_VALID(). For validation against a schema, see JSON_SCHEMA_VALID().

Advertisement