ISJSON Function in SQL Server - SQL Server Guides

In this SQL Server tutorial, I will show you how to use the ISJSON function in SQL Server to check whether a given string is JSON value or not.

JSON is the most popular data exchange format used on the web to exchange data from one place to another.

I will describe the exact working of the ISJSON() function, also ‘What is JSON?’, and then the syntax of how to use the ISJSON function.

After that, in some examples, you will learn how to check whether the given value is a valid JSON value. After that, you will use the different types of JSON data, such as JSON array and scalar with ISJSON function in SQL Server.

Ultimately, you will learn to validate variables to see if they contain valid JSON data.

Table of Contents

What is the ISJSON function in SQL Server?

The ISJSON function in SQL Server checks whether a given string value contains JSON data. If the string contains JSON data, it returns 1; otherwise, 0 indicates invalid JSON data.

JSON is Javascript Object Notation, which is the file format that helps store and transport data across devices and is generally used for data exchange purposes.

This JSON object consists of key-value pairs. For example, look at below.

{
"state": 'USA",
 "name": "Ben",
"age" : 23
}

As you can see, there are curly braces, and within that, a key-value pairs data; here, the left-side value is the key, and the right side is the value. For example, state, name, and age are key, and their values are ‘USA’, ‘Ben’, and 23, respectively.

A detailed explanation of JSON is not part of this tutorial, so you must understand how JSON data looks from the above.

The syntax of how to use the ISJSON() function is given below.

ISJSON(expression);

Where,

ISJSON(): The function accepts the expression and tells whether it is valid JSON data.
expression: It is a string that contains the JSON value. The valid JSON value can be an array, a literal string (null, true, false), or an object.

The return type of the ISJSON() function is 1 if the expression contains valid JSON data; otherwise, it is 0.

For example, if you have the string {“City”: “Los Angeles”}, now pass this string value to the ISJSON() function to see if it is valid JSON or not. For that, use the query below.

SELECT ISJSON('{"City": "Los Angeles"}') AS JsonValue;

For the string {“City”: “Los Angeles”}, the ISJSON() function returns 1, which means this string value contains the valid JSON data. Also, you can see that the string contains the curly braces{}; within that, there is a key-value pair. The key is “City”, and the value is “Los Angeles”.

Next, pass the only string to this function and see how it responds. For example, pass only the ‘Los Angeles’ string, as shown below.

SELECT ISJSON('{"Los Angeles"}') AS JsonValue;

ISJSON Function in SQL Server Passing String Value

When you pass the string value within curly braces, it checks and returns 0, indicating an invalid JSON value.

This is how you can validate whether the given expression is JSON data using the ISJSON function in SQL Server.

ISJSON Function in SQL Server with Array, Scalar

You can even pass the array to check the valid JSON array; if you don’t know how a JSON array looks, then see below.

["USA", "Canada", "Australia"]

In the above JSON array, each string with brackets is wrapped with a double single quote.

Let’s pass the above JSON array as a string to see if it is valid according to the ISJSON() function.

SELECT ISJSON('["USA", "Canada", "Australia"]') AS JsonArrayValue;

ISJSON Function in SQL Server with Array

The above string contains the valid JSON array. As you can see in the output, the result is 1, which is how you can verify whether a given string contains a valid JSON array.

Next, let me show you how to check whether the string contains the valid JSON scalar value. The scalar value is a number or string.

For example, if you have a number like this, ’42’, now pass it to the ISJSON() function to check whether it is a valid JSON scalar.

SELECT ISJSON('42', scalar) AS JsonScalarValue;

ISJSON Function in SQL Server Passing Scalar Value

As you can see, the given string value contains a valid JSON scalar number because ISJOSN() returns 1. But here, within the ISJOSN() function, one more value is passed: ‘ scalar’.

This extra value is called JSON type constraint, which means JSON type of the input value; in this case, the type of passed value is scalar, so the second argument is scalar.

This is how to see if the given expression is a valid JSON scalar or not using the ISJSON function in SQL Server.

Using ISJSON Function in SQL Server with Variable

If the JSON data is stored in a variable, you can also validate the variable value for valid JSON data.

For example, there is a JSON object in the @Details variable that you can see in the query below.

DECLARE @Details NVARCHAR(4000)  
SET @Details = N'{"data":[  
{"Name":"Adam", "Age":30} 
]}'  
SELECT ISJSON(@Details) AS JsonData;

Using ISJSON Function in SQL Server with Variable

From the output, the result set contains 1, which means the given variable contains the valid JSON object.

Here, in the query part DECLARE @Details NVARCHAR(4000), a variable named @Details is declared of type nvarchar of length 4000.

Then, the JSON object N'{“data”:[ {“Name”:”Adam”, “Age”:30} ]}’ is assigned to this variable as the string using the SET keyword.

As a result, when this variable is passed to the ISJSON() function, the function returns 1, which means it is a valid JSON value.

Conclusion

In this SQL Server tutorial, you have covered how to check whether the given string contains the JSON value using the ISJSON function in SQL Server.

After that, you have passed the JSON array and scalar to the ISJSON() function to determine whether they are valid JSON data.

Finally, you learned to use the variable with the ISJSON function in SQL Server.