SQL - ISJSON() Function

---

---

The built-in SQL ISJSON() function is used to check a string's validity for JSON (JavaScript Object Notation) syntax. JSON is a simple format for exchanging data between applications.

The ISJSON function accepts a string parameter as input and returns a bit value indicating whether or not the string is in the JSON format. The function returns 1 if the supplied string is valid JSON; else, it returns 0.

Syntax

Following is the syntax of the SQL ISJSON() function −

ISJSON ( expression [, json_type_constraint] )

Parameters

This function accepts only two parameter. The same is described below −

• expression − It is the string expression that we are testing for valid JSON.

• json_type_constraint − It specifies the JSON type to check in the input.

Example

In the following example, we are going to check the output by passing the valid JSON by using the following query −

SELECT ISJSON('{"car": "RX100"}') AS Result;

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                                  1 |
+------------------------------------+

Example

Let's consider another scenario where we are passing the valid input and checking the output by using the following query −

SELECT ISJSON('true', VALUE) AS Result;

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                                  1 |
+------------------------------------+

Example

Let's look into the another example, where we are going to check the valid JSON on declaring the string by using the following query −

DECLARE @text Varchar(245) = '[{"Id":0,"Name":"TP"}]'
   IF ISJSON ( @text ) = 1
      PRINT 'Valid JSON'

Output

When we execute the above query, the output is obtained as follows −

Valid JSON

Example

In the following example, we are going to check the output when the string doesn't contain valid JSON by using the following query −

SELECT ISJSON('car: RX100') AS Result;

Output

On executing the above query, the output is displayed as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                                  0 |
+------------------------------------+

Example

Let's consider another example, where we are going to check the output on passing the invalid JSON by using the following query−

SELECT ISJSON('test', VALUE) AS Result;

Output

On executing the above query, the output is displayed as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                                  0 |
+------------------------------------+

Example

Considering the following example, where we are going to pass NULL to the function and going to check the output by using the following query −

SELECT ISJSON(NULL) AS Result;

Output

On executing the above query, the output is displayed as follows −

+-------------------+
|            Result |
+-------------------+
|         NULL      |
+-------------------+

Example

Let's look into the following example, where we are going to use the basic CONDITIONAL STATEMENTand retriveing the output depending on the string that contains JSON or not and the query was stated below −

DECLARE @work varchar(255);
SET @work = '{"car": RX100}';
IF (ISJSON(@work) > 0)
   SELECT 'Valid JSON' AS 'Result';
ELSE
   SELECT 'Invalid JSON' AS 'Result';

Output

The output for the above query is produced as given below −

+------------------------------------+
|                              Result|
+------------------------------------+
|                       Invalid Json |
+------------------------------------+