… IS JSON [ARRAY|OBJECT|SCALAR]

1. a⚡Does not recognize JSON scalars as valid JSON
2. bNo type constraints: … is json [array|object|scalar]
3. cAccepts unquoted object keys: {a: 1}

Is json is a predicate, similar to is null, to test something for valid JSON content. The test can also distinguish between Arrays ([1,42]), Objects ({"a":42}) and scalar values (strings, numbers, true, false, null).

WHERE c IS JSON OBJECT

The example picks rows for which the column c contains a valid JSON structure of which the topmost element is a JSON object.

As is json never fails it can be used to test the contents of string values for well-formed JSON contents. If the input is the SQL null value, the result is also the SQL null (unknown) value.

The type constraints array, object and scalar make it even useful for values of the type JSON. If no type constraint is specified, value is implied—which returns true for all valid JSON data.

1. aAccepts unquoted object keys: {a: 1}
2. bNot for character strings

Is json does not allow to check for the specific scalar types string, numeric, boolean or the null values (the SQL/JSON Path item method .type() supports that).

Negation can be done with is not json. Here it has to be noted that is not json object returns true for non-JSON data as well as for valid JSON data that does not have an object at the top level.

Formats and Dialects

SQL uses the JSON format defined by IETF RFC 8259. This JSON format allows a single JSON object to contain the very same key value multiple times:0

{"a": 42,
 "a": 1}

Therefore, the is json predicate accepts such objects as valid JSON. For environments that require a more rigid validation the standard provides the with unique [key] option that causes is json to treat objects with duplicate keys as invalid JSON.

Further, the SQL standard defines a syntax to specify another data format such as BSON or AVRO by postfixing the tested expression with the format clause. In absence of a format clause format json is implied with refers to the the RFC 8259 format.

1. aAccepts unquoted object keys: {a: 1}
2. bFails on non-json input

The SQL standard defines only one format: json. Other formats and their names are implementation-defined (IV180).

Alternatives

Optional Features

• T821, “Basic SQL/JSON query operators”

• T822, “SQL/JSON: IS JSON WITH UNIQUE KEYS predicate”

• T832, “SQL/JSON path language: item method”

  • .type() item method

• T851, “SQL/JSON: optional keywords for default syntax”

Alternatives

• isjson() function

• json_valid() function

• safe.parse_json() function

Implementation-Defined Items

• IV180,“The data format specified by <implementation-defined JSON representation option>”

Tutorials

• JSON in SQL:2016

Normative References

The <JSON predicate> is [not] json [value|array|object|scalar] is defined in ISO/IEC 9075-2:2023 as part as the optional feature T821, “Basic SQL/JSON query operators”.

You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Bluesky or RSS to gradually catch up and to keep modern-⁠sql.com on your radar.

About the Author