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 conforming0
if the input string is not valid JSON dataNULL
if the input expression is NULLThe 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 example:
USE WideWorldImporters; DROP TABLE IF EXISTS dbo.Users;