SQL Server ISJSON() Function

Summary: in this tutorial, you will learn how to use the SQL Server ISJSON() function to check if a string contains valid JSON.

Introduction to the SQL Server ISJSON() function #

The ISJSON() function checks if a string contains valid JSON.

Here’s the syntax of the ISJSON() function:

ISJSON ( expression [, json_type_constraint] )

In this syntax:

expression is a string to check, which can be a literal string, a variable, or a table column.
json_type_constraint is the JSON type to check the expression. The valid values of the json_type_constraint are VALUE, ARRAY, OBJECT, or SCALAR. The json_type_constraint is optional. If you omit the json_type_constraint, the function checks if the input is a JSON object or array.

The following table shows the detailed meaning of each value:

Value	Meaning
`VALUE`	Check if the input string is a valid JSON value, which can be a JSON object, array, number, string, bool, or null.
`ARRAY`	Check if the input string is a valid JSON array.
`OBJECT`	Check if the input string is a valid JSON object.
`SCALAR`	Check if the input string is a scalar value.

The ISJSON() function returns 1 if the string contains valid JSON or 0 otherwise. If the expression is null the ISJSON() function returns null.

SQL Server ISJSON() function examples #

Let’s take some examples of using the ISJSON() function.

1) Basic SQL Server ISJSON() function examples #

The following example uses the ISJSON() function to check if a string contains a valid JSON object:

SELECT ISJSON('{}') AS result;

Output:

result
------
1

The following example uses the ISJSON() function to determine where a string contains a valid JSON array:

SELECT ISJSON('[]') AS result;

Output:

result
------
1

The following statement returns 0 because the string does not contain valid JSON:

SELECT ISJSON('Hi') AS result;

Output:

result
------
0

To check if the string “Hi” is a JSON scalar value, you need to use the json_constraint_type SCALAR as follows:

SELECT ISJSON('"Hi"',SCALAR) AS result;

Output:

result
------
1
(1 row)

The following example returns 0 because the key of the JSON object is surrounded by single quotes:

SELECT ISJSON('{''age'': 10}') result;

Output:

result
------
0

2) Using SQL Server ISJSON() function with table data #

First, create a new table called settings:

CREATE TABLE settings(
   id INT IDENTITY PRIMARY KEY,
   options NVARCHAR(MAX) NOT NULL
);

The options column will store JSON data.

Second, insert some rows into the settings table:

INSERT INTO settings (options)
VALUES
  ('{"theme": "dark", "fontSize": 14, "language": "en"}'),
  ('{"theme": "light", "fontSize": 12, "language": "fr"}'),
  ('{"theme": "auto, "fontSize": 16, "language": "de"}');

Third, validate the JSON data in the options column of the settings table using the ISJSON() function:

SELECT 
  id, 
  options, 
  ISJSON(options) valid 
FROM 
  settings;

Output:

id | options                                              | valid
------------------------------------------------------------------
1  | {"theme": "dark", "fontSize": 14, "language": "en"}  | 1
2  | {"theme": "light", "fontSize": 12, "language": "fr"} | 1
3  | {"theme": "auto, "fontSize": 16, "language": "de"}   | 0

Summary #

Use the ISJSON() function to check where a string contains valid JSON.

Was this tutorial helpful?