JSON (JavaScript Object Notation) Support with SQL Server 2016


There have been many new features introduced with SQL Server 2016 and many enhancements and additional support to new features, etc. One such interesting change with Microsoft SQL Server 2016 is native support for JSON. JSON stands for JavaScript Object Notation. If you are wondering what is this JSON (JavaScript Object Notation), is is an open and text-based data exchange format, that provides a standardized data exchange format better suited for Ajax-style web applications. In other words, JSON is a data interchange format that has become popular for moving data between systems. There was no support for JSON feature prior to SQL Server 2016, so the application developers had to fall on to use third party tools or workarounds to achieve the output in this format.

One of the popular language which many of us are aware for exchanging data between different heterogeneous systems is XML. Since many versions of SQL Server has been supporting XML by allowing queries which deal with XML data to store, retrieve or manipulate the XML data using SQL Server TSQL queries, specifically designed to handle XML data. JSON is another such data format like XML, which is also used to exchange data between different heterogeneous systems. Initially JSON was developed specifically for javascript, but later it has become as an independent language. As Microsoft wants to spread the usage of SQL Server across different platforms, there has been many changes like support to running SQL Server on linux and support for languages like JSON. Although, SQL Server 2016 supports JSON language, but that does not mean, XML is no more supported. Microsoft SQL Server newer versions will continue to support both XML and JSON languages and in future, we may see even more languages added to this list, this will help SQL Server scale new heights and can be used with any applications.

Format of JSON looks like below

   { "name": "Test", "skills":["SQL","C#","Azure"] },
   { "name": "Mike", "surname": "Taite" }

SQL Server offers many built-in functions and operators that lets us do the things like,
– Parse JSON text and read or modify the values.
– Convert arrays of JSON objects into table format.
– Use any TSQL queries on the converted JSON objects.
– Format the results of TSQL queries in JSON format.

Some of the key capabilities that SQL Server provides related JSON include,
– Use the JSON_VALUE function to extract a scalar value from a JSON string
– Use JSON_QUERY to extract an object or an array
– Use the ISJSON function to test whether a string contains valid JSON.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Leave a Reply

Your email address will not be published. Required fields are marked *