SQL SERVER – 2016 – Check Value as JSON With ISJSON()

I have been wanting to write about the various operators that govern to use JSON and my first shot has been with: SQL SERVER – Getting started and creating JSON using SQL Server 2016. That was like a primer and as I see the subsequent releases of SQL Server 2016 CTP’s, I see more capabilities have been added to this. One of the most important tasks to do is to access the value that is represented in a JSON document. In this blog, let me talk about one of the most important function that can be used to validate / address if the document entered in JSON or not with the help of ISJSON().

Why would one be using these? When for cases when the data is being sent from the front end, we want to make sure the document that is sent is a well-structured JSON or not. This is the place this function comes to our help. ISJSON is a deterministic function because the value will not same for the same input that we specify.

If you are using it inside a procedural context, this can be easily checked with the ISJSON function like below:

DECLARE @json NVARCHAR(4000)
SET @json = N'{
"id": 1,
"name": "A green door",
"price": 12.50,
"color": "Green",
"type": "bricks",
"tags": ["home", "green"] }'
IF (ISJSON(@json) = 1)
BEGIN
PRINT 'Got it :)'
END
GO

The above code will print “Got it :)” on the console or SQL Server Management Studio Output window. Now the same function can be used while we enter data into a SQL Server table with a check constraint.

-- With a CREATE TABLE Syntax
CREATE TABLE MyJSONTable(
Id INT,
jsonColumn VARCHAR(MAX) NULL CHECK (ISJSON(jsonColumn)>0)
);
-- Incase we need to add this to an existing table
ALTER TABLE MyJSONTable
ADD CONSTRAINT Chk_MyJSONTable_jsonColumn_is_json CHECK (ISJSON(jsonColumn) > 0);

This is a unique and interesting way to is ISJSON constructs inside SQL Server. The value is 1 is it is TRUE and returns 0 when the JSON document is not a well-constructed. On the sidelines, I wanted to see what will it return if we send a NULL. The function returns a NULL.

I thought this is an interesting learning worth to share when working with JSON documents inside SQL Server 2016 release. I hope you will have better and more complex situations by working with JSON documents which you can share via comments on its usage. Let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)