ISJSON enhancements in SQL Server for valid JSON format for a Value, Array or Object

By: Daniel Calbimonte   |   Updated: 2023-05-25   |   Comments   |   Related: > Functions System

Problem

SQL Server supports working with JSON data and provides many different functions that can be used. SQL Server 2022 has expanded the ability to check if the JSON format is valid using the ISJSON function which we will cover in this article.

Solution

Following is information about the enhancements to the ISJSON function along with several examples.

ISJSON Function

This function is a string validator of JSON values. It will return the value of 0 if the JSON is invalid and 1 if the JSON string is valid. The function is useful to validate your JSON and if it contains invalid data the function will detect it.

Changes in SQL Server 2022 allow you to validate whether it is a valid value, array, or object.

The syntax is the following:

ISJSON ( value or expression, type )

Value or expression is a value or a T-SQL expression that will be evaluated. The type is a new argument valid in SQL Server 2022.

The JSON type can be:

• value
• array
• object
• scalar

Examples of ISJSON Function with Values

The following example shows a typical error:

SELECT ISJSON(33, value) as isvalid

Argument data type int is invalid as a value. This error occurs because the ISJSON function does not support numeric values.

The following example shows how to fix the problem.

SELECT ISJSON('33', value) as isvalid

This query will return the value of 1, which means that it is a valid value.

The following example shows invalid values:

SELECT ISJSON('33,33', value) as isvalid

The next example shows what happens when checking arrays:

SELECT ISJSON('[23,34]', value) as isvalid

In this case, the value returned is valid.

Error in Earlier Versions of SQL Server with the ISJSON Function

This function has existed since SQL Server 2016, however, only one argument was allowed.

The following example illustrates this when running on SQL Server 2019 or earlier versions.

SELECT ISJSON('true', scalar) as isvalid

This returned the following:

Examples of ISJSON Function with Arrays

The following example shows if the value is a valid array.

SELECT ISJSON('23', array) as isvalid

The function will return 0, which means that it is invalid.

On the other hand, the following example will return 1.

SELECT ISJSON('[23,34]', array) as isvalid

You can also work with variables and store the JSON in a variable and work with the variable directly. The following example shows a list of the top NBA player names of all time.

DECLARE @json NVARCHAR(MAX) = N'["Michael", "Kobe", "LeBron", "Magic", "Larry", "Kareem", "Wilt", "Bill", "Shaquille", "Tim"]';
SELECT ISJSON(@json, array) as isvalid;

Note: If you use the argument with double quotes "array", it is still valid:

DECLARE @json NVARCHAR(MAX) = N'["Michael", "Kobe"]';
SELECT ISJSON(@json, "array") as isvalid;

However, if you use single quotes 'array', an error will be displayed:

DECLARE @json NVARCHAR(MAX) = N'["Michael", "Kobe"]';
SELECT ISJSON(@json, 'array') as isvalid;

The error message is the following:

What happens if I have objects, and one of the objects has an array of attributes? Will the ISJSON function consider the array valid? Let’s take a look:

DECLARE @json NVARCHAR(MAX) = N'{
  "name": "John",
  "age": 30,
  "city": "New York",
  "pets": [
    {
      "type": "dog",
      "name": "Buddy"
    },
    {
      "type": "cat",
      "name": "Lucy"
    }
  ],
  "family": {
    "father": {
      "name": "Peter",
      "age": 60
    },
    "mother": {
      "name": "Mary",
      "age": 55
    }
  }
}';
SELECT ISJSON(@json, array) as isvalid;

In this example, the object is John. We have his age, city, pets, and family. The pet is an array with two pets, a cat and a dog. However, the main type is an object which contains arrays. That is why the select will return an invalid value:

On the other hand, if we validate that the JSON is a valid object, it will return a value equal to true (1).

DECLARE @json NVARCHAR(MAX) = N'{
  "name": "John",
  "age": 30,
  "city": "New York",
  "pets": [
    {
      "type": "dog",
      "name": "Buddy"
    },
    {
      "type": "cat",
      "name": "Lucy"
    }
  ],
  "family": {
    "father": {
      "name": "Peter",
      "age": 60
    },
    "mother": {
      "name": "Mary",
      "age": 55
    }
  }
}';
SELECT ISJSON(@json, object) as isvalid;

In the previous example, we verified that the JSON string was a valid array, and the result was false. In this example, we verified that the JSON string is a valid object, and the result is true (1).

Examples of ISJSON Function with Scalar Values

The following example will return a false value because the first argument is not a JSON scalar value.

SELECT ISJSON('[23,34]', scalar) as isvalid

The next example shows a valid scalar value.

SELECT ISJSON('34', scalar) as isvalid

If we provide numeric values:

SELECT ISJSON(34, scalar) as isvalid

The function will fail

Note: True and false values are not valid scalar values.

SELECT ISJSON('true', scalar) as isvalid

String values are also invalid scalar values.

SELECT ISJSON('myvalue', scalar) as isvalid

Example using IF Function and ISJSON Function

The following example shows how to combine IF with ISJSON. This example will display a message if the value is 1 and another message if the value is 0.

IF ISJSON('myvalue', scalar) = 1
SELECT 'The value is valid' as result
ELSE
SELECT 'The value is invalid' as result

Examples of ISJSON Function with Object Values

The following example shows a valid object value.

SELECT ISJSON('{"name":"daniel"}', object) as isvalid

The next example shows how to work with null values.

SELECT ISJSON('{"name":null}', object) as isvalid

Here is an example that shows a valid object with a numeric value.

SELECT ISJSON('{"name":1}', object) as isvalid

Finally, this example shows an invalid object.

SELECT ISJSON('{"value"}', object) as isvalid

Conclusion

This tip discussed the new arguments added to the ISJSON function. The type was added to the function and now in SQL Server 2022 we can validate if it is a valid value, scalar, array, or object. JSON is very popular these days, so we may see even more functions and improvements in the coming versions of SQL Server.

Next Steps

To learn more about JSON in SQL Server, please refer to these links:

• Introducing JSON for SQL Server 2016
• JSON Support in SQL Server 2016
• Advanced JSON Techniques in SQL Server - Part 1
• Advanced JSON Techniques in SQL Server - Part 2
• Including NULL Values in JSON Output in SQL Server 2016

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips