The ISJSON(…, <type constraint>) Function

1. Supports the standard is json test
2. ⚡Does not recognize JSON scalars as valid JSON
3. No type constraints: … is json [array|object|scalar]

The isjson function returns 1 if the argument string contains valid JSON. Otherwise it returns null if the input was null or 0 for any non-null non-JSON value.

1. Differs from standard SQL is json: 1 for JSON arrays and objects only
2. Differs from standard SQL is json: 1 for JSON numbers and strings only

Similar to the is json test of standard SQL isjson allows objects to contain the same key several times.

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

Related

Product Documentation

• Microsoft SQL Server: ISJSON (Transact-SQL)

Standard SQL Features

• T821, “Basic SQL/JSON query operators”

  • … Is Json [Array|Object|Scalar]: Test for Valid JSON, Distinguish between Arrays, Objects and Scalars

  • 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”

Non-Standard Alternatives

• json_valid() function

• safe.parse_json() function

Tutorials

• JSON in SQL:2016

Normative References

The isjson function is not covered by the international SQL standard ISO/IEC 9075-2:2023.