Advanced JSON Techniques in SQL Server 2016 - Part 3

By:   |   Updated: 2016-01-07   |   Comments   |   Related: More > SQL Server 2016


In previous tips, Advanced JSON Techniques in SQL Server 2016 (Part 1 and Part 2), I gave examples of several JSON functions in SQL Server 2016, as well as how to create an index for efficient searching of text within a JSON document. In this tip, I want to show you a new keyword and a new function, both of which help to control JSON output.



In CTP 3.2, Microsoft added a new option to allow you to suppress the array wrappers around JSON output. For example, if you run this query:

SELECT TOP (2) name 
  FROM sys.all_objects 
  ORDER BY name

You get this result, which includes [square brackets] around the entire document:


But now with the new option, WITHOUT_ARRAY_WRAPPER, you can run this query:

SELECT TOP (2) name 
FROM sys.all_objects 

And you get results that are slightly different (namely, they are missing the outer square brackets):


This can be important if, for example, you are extracting bits of data to combine into larger JSON strings.

JSON_QUERY() SQL Server Function

This new function allows you to retrieve an object or an array from within a larger JSON string. Let's say we have this simple table:

  Attributes NVARCHAR(4000),

And this rather simplistic sample data, having several attributes for a couple of cars:

INSERT dbo.Cars(CarID, Attributes)
  VALUES(1, '{"year":1973,"make":"Datsun","model":"B210",
    "features": [{"power windows":1}]}'),
        (2, '{"year":1974,"make":"Datsun","model":"B210",
    "features": [{"power windows":1},{"block heater":1}]}');

We can use JSON_QUERY() to just pull out the set of features for each car:

SELECT CarID, Features = JSON_QUERY(Attributes, '$.features')
  FROM dbo.Cars;

These results show the set of features for each car:

CarID	Features
-----	----------------------------------------
1	[{"power windows":1}]
2	[{"power windows":1},{"block heater":1}]

We can break it down further to a feature per row, by using CROSS APPLY():

SELECT c.CarID, x.[value]
  FROM dbo.Cars AS c
  CROSS APPLY OPENJSON(JSON_QUERY(Attributes, '$.features')) AS x;

The results, though, still contain some of the JSON scaffolding:

CarID	value
-----	-------------------
1	{"power windows":1}
2	{"power windows":1}
2	{"block heater":1}

You could really turn this into a set-based result by using OPENJSON() again:

SELECT c.CarID, y.[key], y.[value]
  FROM dbo.Cars AS c
  CROSS APPLY OPENJSON(JSON_QUERY(Attributes, '$.features')) AS x
  CROSS APPLY OPENJSON(x.[value], '$') AS y;

These results show the output as proper key-value pairs:

CarID	key		value
-----	-------------	-----
1	power windows	1
2	power windows	1
2	block heater	1

Now you can query around that to find all the cars with power windows, all the cars without a block heater, all the cars with at least ten features, and all sorts of other things - with far less messy code, and no parsing.

Jovan Popovic has some more background and examples here:

Next Steps

Last Updated: 2016-01-07

get scripts

next tip button

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

View all my tips
Related Resources

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Recommended Reading

New Drop If Exists Syntax in SQL Server 2016

New CREATE OR ALTER statement in SQL Server 2016 SP1

SQL Server 2016 Always Encrypted

Automatic Updates for SQL Server Management Studio

Advanced Techniques to Transform Relational Data to JSON in SQL Server 2016

get free sql tips
agree to terms

Learn more about SQL Server tools