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.