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;