Select Page

Native JSON Support Gets Even Better With SQL Server 2016

Author: 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

A sample output for the above query would look like:


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


“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

The output for the above query would look like below:



“JobTitle”:”Chief Executive Officer”},


“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.

    [, ROOT[('<RootName>')]]

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

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



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



“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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

Using Nulls in DB2

If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known.

Craig Mullins | April 6, 2015

MongoDB Best Practices: Design, Deployment & More

This post provides a rundown of best practices to use when running MongoDB.

Esayas Aloto | February 28, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

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


Work for Us

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