Native JSON Support Gets Even Better With SQL Server 2016

Every five years we seem to see technology innovations that rock the industry and I am amazed to see how we seem to have to make a fresh start every other year. About 15 years back, I still remember that XML was brought to the industry. It was evolving and I still remember XML took mainstage when web services, WSDL and other technologies took off. In the recent past, with WebAPI and other message interchange formats I have started to see JSON as a famous format commonly used.

As SQL Server 2016 is now in the Community Technology Preview, one of the most awaited features is the support for JSON. In this blog we’ll walk through some of the common methods of creating JSON documents inside SQL Server natively.

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.

SELECT Top 2 pp.FirstName, emp.JobTitle
FROM Person.Person pp
JOIN 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

The next option is to add a root node to an output as we form our JSON document. This is option can be enabled using the PATH option 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 below:

{“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 of 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 and 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 use case of using the various output formats with JSON creation inside SQL Server 2016. This in no way is an exhaustive list of options that we can use JSON documents. I am sure as we move into CTP3 and towards RTM, the number of options and capabilities are going to increase. Also, let me know how you will be using JSON documents inside SQL Server 2016 when it is released. Are there workloads that will benefit from this capabilities? I can see this being useful as part of modern day applications design.

Is it time for an upgrade?

SQL Server 2016 is no longer the latest version. To unlock even more SQL server capabilities and features, consider making the move to SQL Server 2017. Download our SQL Server 2017 white paper to learn more.