Validating JSON data

To validate JSON, you can use the ISJSON function. This is a scalar function and checks whether the input string is valid JSON data. The function has one input argument:

• string: This is an expression of any string data type, except text and ntext

The return type of the function is int, but only three values are possible:

• 1 if the input string is JSON conforming

• 0 if the input string is not valid JSON data

• NULL if the input expression is NULL

The following statement checks whether the input variable is JSON valid:

SELECT  
  ISJSON ('test'),  
  ISJSON (''),  
  ISJSON ('{}'),  
  ISJSON ('{"a"}'),  
  ISJSON ('{"a":1}'), 
  ISJSON ('{"a":1"}');

Here is the output:

------ ------ ------ ------ ------ ------
0      0      1      0      1      0

ISJSON does not check the uniqueness of keys at the same level. Therefore, this JSON data is valid:

SELECT ISJSON ('{"id":1, "id":"a"}') AS is_json; 

It returns:

is_json
-----------
1

Since there is...