Every five years we see technological innovations that rock the industry. About 15 years back, XML was brought to the industry. It was evolving and took main stage when web services, WSDL and other technologies took off. With WebAPI and other message interchange formats, we have started to see JSON as the most famous and commonly used format.

One of the most awaited features of SQL Server 2016 is the support for JSON. In this chapter we’ll walk through some of the common methods of creating JSON documents inside SQL Server natively. Please note, however, that built-in JSON support is not the same as the native JSON.

In SQL Server 2016, JSON will be represented as NVARCHAR type. NVARCHAR is supported in all SQL Server components, so JSON will also be supported in all SQL Server components. "If NVARCHAR works with feature X then JSON will also work," according to the Microsoft blog.

Getting a JSON Document

The simplest method to work with JSON includes the FOR JSON format with the SELECT statement. This clause delegates the formatting of the SELECT statement as a JSON output to be combined with application.

Using AUTO Output

This is the simplest form to start a query. We are using the auto-magical capability of generating the JSON. JSON AUTO automatically creates nested JSON sub arrays based on the table hierarchy used in the query.

SELECT Top 2 pp.FirstName, emp.JobTitle
FROM Person.Person pp HumanResources.Employee emp ON pp.BusinessEntityID = emp.BusinessEntityID
FOR JSON AUTO

A sample output for the above query would look like:
[{“FirstName”:”Ken”,

“emp”:[{“JobTitle”:”Chief Executive Officer”}]},

{“FirstName”:”Terri”,

“emp”:[{“JobTitle”:”Vice President of Engineering”}]

}]

Using a PATH Output

JSON PATH enables you to define the structure of output JSON using the column names/aliases. If you put dot-separated names in the column aliases, for example, JSON properties will follow the naming convention. This is similar to the way XML PATH allows you to add forward slashes for separated paths.

To add a root node to an output as we form our JSON document, we would use FOR JSON PATH, as shown below:

SELECT Top 2 pp.FirstName, emp.JobTitle
FROM Person.Person pp JOIN HumanResources.Employee emp ON pp.BusinessEntityID = emp.BusinessEntityID
FOR JSON PATH, ROOT ('People')

The output for the above query would look like this:
{“People”:[

{“FirstName”:”Ken”,

“JobTitle”:”Chief Executive Officer”},

{“FirstName”:”Terri”,

“JobTitle”:”Vice President of Engineering”} ]}

When working with this dataset, I was faced with a requirement to take care of NULL values. In the initial days, forming XML data was a big pain. You would have had to use the ISNULL function in front of every column. But with JSON document path, we have an option to take care of it using an option as part of the predicate.

<<SELECT QUERY>>
FOR JSON AUTO|PATH [, ROOT[(' <<RootName> ')]] [, INCLUDE_NULL_VALUES]

Specify the Path

An advanced option to this requirement of JSON document would be to build our own custom format. This can be easily enabled with the PATH predicate, then using the alias as shown below.

SELECT Top 2 pp.FirstName AS "People.FName", emp.JobTitle AS "People.Job.Title"
FROM Person.Person pp JOIN HumanResources.Employee emp ON pp.BusinessEntityID = emp.BusinessEntityID
FOR JSON PATH

In our query above you can see how the JobTitle has been nested in the output:

[{“People”:

{“FName”:”Ken”,

“Job”:{“Title”:”Chief Executive Officer”}}},

{“People”:

{“FName”:”Terri”,

“Job”:{“Title”:”Vice President of Engineering”}}

}]

This is a typical case of using the various output formats with JSON creation inside SQL Server 2016.

Converting JSON Text into Relational Data

JSON text that is generated with FOR JSON clause can be transformed back to the relational form using OPENJSON. This feature will be available in CTP3. OPENJSON will be available to parse different formats of JSON text (e.g. nested JSON objects). The syntax is nearly the same as OPENXML functions.

What can you do with OPENJSON? The Microsoft blog explains:

Imagine that you are importing JSON documents in database and you want to load them in a table. Instead of parsing JSON at the client side and streaming set of columns to table, you can send JSON as-is, and parse it in database layer.

This in no way is an exhaustive list of options for using JSON documents. We are sure as we move into CTP3 and towards RTM, the number of options and capabilities are going to increase.