Edit

Share via

ISJSON (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later versions Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

The ISJSON syntax tests whether a string contains valid JSON.

Transact-SQL syntax conventions

Syntax

ISJSON ( expression [, json_type_constraint] )

Arguments

expression

The string to test.

json_type_constraint

Specifies the JSON type to check in the input. Valid values are VALUE, ARRAY, OBJECT, or SCALAR. Introduced in SQL Server 2022 (16.x).

Note

The argument json_type_constraint is not supported in Azure Synapse Analytics Dedicated pools

Return value

Returns 1 if the string contains valid JSON; otherwise, returns 0. Returns NULL if expression is null.

If the statement omits json_type_constraint, the function tests if the input is a valid JSON object or array and returns 1 otherwise, it returns 0.

If the json_type_constraint is specified then the function checks for the JSON type as follows:

Value Description
VALUE Tests for a valid JSON value. This can be a JSON object, array, number, string or one of the three literal values (false, true, null)
ARRAY Tests for a valid JSON array
OBJECT Tests for a valid JSON object
SCALAR Tests for a valid JSON scalar – number or string

The json_type_constraint value SCALAR can be used to test for IETF RFC 8259 conformant JSON document that contains only a JSON scalar value at top level. A JSON document that doesn't contain a JSON scalar value at top level conforms with IETF RFC 4627.

Does not return errors.

Remarks

ISJSON does not check the uniqueness of keys at the same level.

Examples

Example 1

The following example runs a statement block conditionally if the parameter value @param contains valid JSON.

DECLARE @param <data type>
SET @param = <value>

IF (ISJSON(@param) > 0)  
BEGIN  
     -- Do something with the valid JSON value of @param.  
END

Example 2

The following example returns rows in which the column json_col contains valid JSON.

SELECT id, json_col
FROM tab1
WHERE ISJSON(json_col) = 1

Example 3

The following example returns rows in which the column json_col contains valid JSON SCALAR value at top level.

SELECT id, json_col
FROM tab1
WHERE ISJSON(json_col, SCALAR) = 1

Example 4

The following example returns 1 since the input is a valid JSON value - true.

SELECT ISJSON('true', VALUE)

Example 5

The following example returns 0 since the input is an invalid JSON value.

SELECT ISJSON('test string', VALUE)

Example 6

The following example returns 1 since the input is a valid JSON scalar according to RFC 8259.

SELECT ISJSON('"test string"', SCALAR)

Related content