Select Page

Native JSON Support Gets Even Better With SQL Server 2016

Author: Pinal Dave | 4 min read | 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 Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

PostgreSQL vs. Oracle: Let’s Compare

Discover some key differences between PostgreSQL vs Oracle that can help you make an informed decision when deciding on a database management system.

Vijay Muthu | March 30, 2021

Oracle RMAN Backup and Recovery with Restore Points

Oracle restore points are useful for benchmark testing. Find out how you can use Oracle’s Recovery Manager (RMAN) tool to create and use restore points.

Cindy Putnam | May 3, 2019

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.