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 —
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.
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 —
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 —
In my upcoming blog posts, we will go through the other JSON Functions in detail.