ISJSON (Transact-SQL)
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
Tests whether a string contains valid JSON.
Transact-SQL syntax conventions
ISJSON ( expression [, json_type_constraint] )
The string to test.
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
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.
ISJSON
does not check the uniqueness of keys at the same level.
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
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
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
The following example returns 1 since the input is a valid JSON value - true.
SELECT ISJSON('true', VALUE)
The following example returns 0 since the input is an invalid JSON value.
SELECT ISJSON('test string', VALUE)
The following example returns 1 since the input is a valid JSON scalar according to RFC 8259.
SELECT ISJSON('"test string"', SCALAR)