When using SQL Server, you can use the ISJSON() function to test whether or not a string expression contains valid JSON.

If the expression contains valid JSON, ISJSON() returns 1, otherwise it returns 0.

From SQL Server 2022, we also have the option of testing for a specific JSON type.

Syntax

The syntax prior to SQL Server 2022 goes like this:

ISJSON ( expression )

Where expression is the string expression for which you’re testing for valid JSON.

From SQL Server 2022 (16.x), the syntax goes like this:

ISJSON ( expression [, json_type_constraint] )

The json_type_constraint argument specifies the JSON type to check in the input. Valid values are VALUE, ARRAY, OBJECT or SCALAR.

Example 1 – Valid JSON

Here’s an example to demonstrate what happens when the string contains valid JSON.

SELECT ISJSON('{"Name": "Bob"}') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

Example 2 – Invalid JSON

Here’s an example to demonstrate what happens when the string doesn’t contain valid JSON.

SELECT ISJSON('Name: Bob') AS Result;

Result:

+----------+
| Result   |
|----------|
| 0        |
+----------+

Example 3 – A Conditional Statement

Here’s a basic conditional statement that outputs a different result, depending on whether the string contains JSON or not.

DECLARE @data nvarchar(255);
SET @data = '{"Name": "Bob"}';
IF (ISJSON(@data) > 0)
    SELECT 'Valid JSON' AS 'Result';
ELSE
    SELECT 'Invalid JSON' AS 'Result';

Result:

+------------+
| Result     |
|------------|
| Valid JSON |
+------------+

Example 4 – A Database Example

In this database query, the results are only returned where the Collections.Contents column contains valid JSON.

This particular column uses a data type of nvarchar(4000) to store the JSON document.

SELECT Contents
FROM Collections
WHERE ISJSON(Contents) > 0;

Result:

+------------+
| Contents   |
|------------|
| [
    {
        "ArtistName": "AC/DC",
        "Albums": [
            {
                "AlbumName": "Powerage"
            }
        ]
    },
    {
        "ArtistName": "Devin Townsend",
        "Albums": [
            {
                "AlbumName": "Ziltoid the Omniscient"
            },
            {
                "AlbumName": "Casualties of Cool"
            },
            {
                "AlbumName": "Epicloud"
            }
        ]
    },
    {
        "ArtistName": "Iron Maiden",
        "Albums": [
            {
                "AlbumName": "Powerslave"
            },
            {
                "AlbumName": "Somewhere in Time"
            },
            {
                "AlbumName": "Piece of Mind"
            },
            {
                "AlbumName": "Killers"
            },
            {
                "AlbumName": "No Prayer for the Dying"
            }
        ]
    }
]            |
+------------+

Example 5 – Checking the JSON Type

From SQL Server 2022, we have the option of checking the JSON type. We can do this by passing the type as a second argument:

SELECT 
  ISJSON('{"Name": "Bob"}', VALUE) AS "value",
  ISJSON('{"Name": "Bob"}', SCALAR) AS "scalar";

Result:

value       scalar     
----------- -----------
          1           0

Here, I checked the same JSON value against two JSON types (VALUE and SCALAR). The value is a VALUE type, and so I got 1 for that type, and I got 0 for the SCALAR type.