What’s on our mind?

Exsilio Solutions is proud to introduce our new blog! Not only is this forum completely integrated with all our social networks, but it is also 100% responsive. Now, you can take Exsilio with you on your phone, tablet, and desktop - redefine what you thought possible!

SQL SERVER 2016 – ISJSON Function

In my first post, How to load data into SQL Server using JSON functionality, I discussed one of the key functions of SQL Server 2016 JSON support, which is OPENJSON.

Today we will see another built in function of JSON, which is ISJSON.

ISJSON - This function is used to test whether a string contains valid JSON or not. It returns 1 if the string is valid JSON. If not, it returns 0. It returns null if the expression is null. We will check all of these return values.

First Valid String Example:

DECLARE @Json nvarchar(max)

SET @Json = N'
    "Type": "Test",
    "Version": 1.0,
    "OrderDetail": {
     "OrderId": "ABC001",
      "ItemPurchase": "Pens",
      "Created": {
        "Date": "2016-07-10",
        "By": "Bob"
IF (IsJSON(@Json) > 0)
PRINT 'Valid JSON String'
             PRINT 'Invalid String'


Second InValid String snip:


Third Null Value Test

SET @Json = NULL



So you can see how we can use this function to find whether a string contains valid JSON Data.

This can be used when data is coming from front end or other sources. Prior to starting the process, you can add validation to check your process in order to catch issues if they exist.

We will be going over more functionality of JSON in our next post until then please share your comments and feedback, thank you.



Comments are closed!

Loading more content...