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] )Code language: SQL (Structured Query Language) (sql)

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:

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
1Code language: SQL (Structured Query Language) (sql)

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

SELECT ISJSON('[]') AS result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
1Code language: SQL (Structured Query Language) (sql)

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

SELECT ISJSON('Hi') AS result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
0Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
1
(1 row)Code language: SQL (Structured Query Language) (sql)

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

SELECT ISJSON('{''age'': 10}') result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
0Code language: SQL (Structured Query Language) (sql)

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
);Code language: SQL (Structured Query Language) (sql)

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"}');Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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"}   | 0Code language: SQL (Structured Query Language) (sql)

Summary #

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