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 no JSON data type and data must be stored...