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.

SQL SERVER - 2016 - Check Value as JSON With ISJSON() ISJSON-NULL-01

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)

JSON, SQL Scripts, SQL Server, SQL Server 2016
Previous Post
SQL SERVER – Boosting User Experience with Analysis Services Perspectives – Notes from the Field #116
Next Post
SQL SERVER – AlwaysOn Availability Group Stuck in Resolving State For Long time

Related Posts

2 Comments. Leave new

  • is there any way to build ISJSON function for sql 2012 ?

    Reply
    • I don’t think Microsoft has given that option in earlier version. You have to write your own user defined function for that.

      Reply

Leave a Reply