Menu

SQL Server ISJSON(): Test Whether a Value Is JSON

ISJSON(expression) tests whether a character expression contains valid JSON. It returns 1 for a valid JSON object or array, 0 for invalid JSON, and SQL NULL when the expression is SQL NULL. Without a type constraint, a top-level JSON number, string, Boolean, or null literal returns 0 even though each is a JSON value.

The function is available in SQL Server 2016 (13.x) and later. The optional json_type_constraint was added in SQL Server 2022 (16.x). The constraint is not supported in Azure Synapse Analytics dedicated SQL pools. See Microsoft’s ISJSON reference.

Syntax

ISJSON(expression [ , json_type_constraint ])

expression is the string to check. In SQL Server 2022 and later, the optional constraint can be VALUE, ARRAY, OBJECT, or SCALAR.

Check a JSON object or array

The one-argument form accepts an object or array at the top level:

SELECT
    ISJSON(N'{"event":"created","id":17}') AS object_is_json,
    ISJSON(N'["created",17]') AS array_is_json,
    ISJSON(N'17') AS number_is_json,
    ISJSON(N'not JSON') AS invalid_is_json,
    ISJSON(CAST(NULL AS nvarchar(max))) AS sql_null_is_json;

object_is_json | array_is_json | number_is_json | invalid_is_json | sql_null_is_json
---------------|---------------|----------------|-----------------|-----------------
1              | 1             | 0              | 0               | NULL

Use ISJSON in a filter to select valid object-or-array text before passing it to another JSON function:

SELECT EventId, Payload
FROM dbo.Events
WHERE ISJSON(Payload) = 1;

If the column uses SQL Server’s native json type, values are validated on input; ISJSON remains useful when checking JSON text stored in character columns or handling external input.

Require a top-level JSON type

SQL Server 2022 and later can check a specific top-level type. VALUE accepts any JSON value, while SCALAR accepts only a JSON number or string:

SELECT
    ISJSON(N'true', VALUE) AS boolean_is_json_value,
    ISJSON(N'true', SCALAR) AS boolean_is_json_scalar,
    ISJSON(N'"ready"', SCALAR) AS string_is_json_scalar,
    ISJSON(N'{"event":"created"}', OBJECT) AS object_is_json_object;

boolean_is_json_value | boolean_is_json_scalar | string_is_json_scalar | object_is_json_object
----------------------|------------------------|-----------------------|----------------------
1                     | 0                      | 1                     | 1

The constraint names are SQL keywords and are written without quotes. VALUE, ARRAY, OBJECT, and SCALAR constraints are unavailable in Azure Synapse Analytics dedicated SQL pools.

What ISJSON does not check

ISJSON checks JSON syntax and, when requested, the top-level JSON type. It does not validate an application schema, and it does not check whether keys are unique within an object. Two properties with the same name can therefore pass this syntax check. If you need values to follow a schema, use explicit constraints in your application or database design.

ISJSON does not throw an error for malformed input; it returns 0. For the other JSON functions and their version differences, see the SQL Server JSON function guide. Microsoft documents syntax and type-constraint behavior in its ISJSON reference.

Advertisement