Built-in JSON Support in SQL Server 2016

By Samir Behara on April 30, 2016 • ( 2 )

Problem Statement –
Lot of web services and Web APIs are using JSON these days, primarily as their data interchange format. The inability of the current versions of SQL Server to parse, store and process JSON data has been a hindrance. This makes the integration of JSON into SQL Server as one of the most awaited feature of SQL Server 2016.

Why JSON?

Javascript Object Notation is a lightweight data interchange format which helps to communicate between client and server side technologies.
Almost all technologies who deal with exchange of data supports JSON.

JSON is considered as the best tool for sharing data, because the data is stored in an array format. This makes data transfer easier since the array structure is pretty much familiar to object oriented languages. In the client side, Javascript is the mostly used language and this format can be easily consumed by javascript and transformed into javascript object.

What are the advantages of JSON over XML?

JSON is lightweight in comparison to XML and has a smaller message size.
In JSON, data is stored in arrays whereas in XML data is stored in trees, hence XML needs to be first transformed before it can be imported.
JSON parsing is generally faster than XML parsing.
Because of the similarity in syntax, JSON is easier to be handled with Javascript.
Formatted JSON is generally easier to read than formatted XML.
Due to its simplicity, JSON runs faster and consumes lesser memory.

To put it out in a very simple way, below is an example of JSON vs XML syntax –

<name first=”Samir” last=”Behara” />

<name>
<first>Samir</first>
<last>Behara</last>
</name>

How to format query results as JSON?

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 will be represented by NVARCHAR datatype.

FOR JSON clause allows us to format query results as JSON text.
Appending this syntax to a standard TSQL query returns the result set in JSON format. Each row will be formatted as one JSON object.

There are 2 types of FOR JSON clause –

FOR JSON AUTO – JSON is formatted by the database engine based on the order of the columns in the SELECT statement.

FOR JSON PATH – JSON is formatted based on the user’s discretion. It gives us full control over the format of the JSON output.

FOR JSON AUTO requires at least one table for generating JSON objects. If you are not using tables, FOR JSON PATH is the only option.

As you see above, SQL returns the JSON data as a single-line value, without any formatting and hence it is a bit difficult to read it.
You can use any of the below mentioned online JSON formatter/validator to convert the JSON into a readable format.

JSON Formatter –
https://jsonformatter.curiousconcept.com/
http://jsonformatter.org/
http://jsonviewer.stack.hu/

How to transform JSON text to relational table?

OPENJSON function can be used to convert JSON text into table rows and columns or to import JSON into SQL tables.

In short, FORJSON converts the table rows into JSON text and OPENJSON converts the JSON text into table rows.

By default, when we use the OPENJSON function, it returns 3 values – key, value and type.

The ‘type’ is represented numerically based on the below table –

Type Value JSON Data Type
0 NULL
1 STRING
2 INT
3 BOOL
4 ARRAY
5 OBJECT

If you want to display the details of a particular element within the ‘Person’ array, you can specify the element number like in the example below —

If you want to view the details of a particular element and flatten it into a single row, you can use the WITH clause, as shown in the example below –

I have uploaded the entire JSON SQL Script, used for the purpose of this article, in this link in GITHUB

Conclusion –

JSON support in SQL Server 2016 will be a great benefit, considering its widespread usage In my next article, we will look at the newly added JSON functions – ISJSON(), JSON_QUERY(), JSON_VALUE(), JSON_MODIFY(), which provides support for handling JSON data.