ISJSON Function in SQL Server 2016


Lot of web services and Web APIs are using JSON these days, primarily as their data interchange format. The inability of the older versions of SQL Server to parse, store and process JSON data has been a hindrance. This makes the integration of JSON tinto SQL Server as one of the most popular features of SQL Server 2016.

SQL Server 2016 provides built-in support for storing, managing and parsing JSON data. One important thing to note is that there is no separate JSON data type created, like XML – rather JSON is represented by NVARCHAR datatype.

In my earlier article, I explained how you can format query results as JSON using ‘FOR JSON‘ clause and how to transform JSON text to relational table using ‘OPENJSON‘ function.
BUILT-IN JSON SUPPORT IN SQL SERVER 2016

In this series of articles, I will explain the various JSON functions in SQL Server 2016 that can be used to handle JSON data —

JSON Functions SQL Server 2016

ISJSON()
When you receive a JSON object from the front end client application, you can do a quick verification in SQL Server side to ensure that the JSON is valid, before storing it into the database. It is always a safe idea to validate the JSON text, before parsing JSON data.

OPENJSON function can be used to convert JSON text into table rows and columns. Before using this function, it is better to validate the correctness of the JSON text.

ISJSON Function

Let us now see a demonstration of the ISJSON() function – 

When you execute the above query in SQL Server Management Studio, you will get the below message stating that it is a valid JSON string —

Is Valid JSON

When you execute the above query in SQL Server Management Studio, you will get the below message stating that it is an invalid JSON string, since I knowingly missed the ‘:’ while specifying the ModifiedDate field —

Is Invalid JSON Object

In my upcoming blog posts, we will go through the other JSON Functions in detail.

3 thoughts on “ISJSON Function in SQL Server 2016

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s