Native JSON Support Gets Even Better With SQL Server 2016

By | In SQL Server | November 24th, 2015

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.

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:

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.

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.

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. What are your thoughts?

Contact Us
Pinal Dave
Consultant to Datavail
Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.

Leave a Reply

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