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 as text, the ISJSON function is important for data validation before the text is saved into a database table. To ensure that a text column stores only JSON- conforming data, you can use the ISJSON function in the check constraint. The following code creates a sample table with a JSON column and an appropriate check constraint:

USE WideWorldImporters; 
DROP TABLE IF EXISTS dbo.Users; 
CREATE TABLE dbo.Users( 
id INT IDENTITY(1,1) NOT NULL, 
username NVARCHAR(50) NOT NULL, 
user_settings NVARCHAR(MAX) NULL CONSTRAINT CK_user_settings CHECK (ISJSON(user_settings) = 1), 
CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (id ASC) 
); 

To test the constraint, you will have to insert two rows in the table. The first INSERT statement contains a well-formatted JSON text, while in the second the value for the last property is omitted; thus the JSON text is invalid. Now, execute the statements:

INSERT INTO dbo.Users(username, user_settings) VALUES(N'vasilije', '{"team" : ["Rapid", "Bayern"], "hobby" : ["soccer", "gaming"], "color" : "green" }'); 
 
INSERT INTO dbo.Users(username, user_settings) VALUES(N'mila', '{"team" : "Liverpool", "hobby" }'); 

The first statement has been executed successfully, but the second, as expected, generated the following error message:

Msg 547, Level 16, State 0, Line 12
The INSERT statement conflicted with the CHECK constraint "CK_user_settings". The conflict occurred in database "WideWorldImporters", table "dbo.Users", column 'user_settings'.
The statement has been terminated.

Ensure that you have dropped the table used in this exercise:

USE WideWorldImporters; 
DROP TABLE IF EXISTS dbo.Users;