Select Page

Native JSON Support Gets Even Better With SQL Server 2016

Pinal Dave | | November 24, 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.

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

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS