Advanced JSON Techniques in SQL Server - Part 3
By: Aaron Bertrand | Comments | Related: 1 | 2 | 3 | More > SQL Server 2016
Problem
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.
Solution
WITHOUT _ARRAY_WRAPPER in JSON
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 FOR JSON PATH;
You get this result, which includes [square brackets]
around the entire document:
[{"name":"all_columns"},{"name":"all_objects"}]
But now with the new option, WITHOUT_ARRAY_WRAPPER
, you can run this query:
SELECT TOP (2) name FROM sys.all_objects ORDER BY name FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
And you get results that are slightly different (namely, they are missing the outer square brackets):
{"name":"all_columns"},{"name":"all_objects"}
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:
CREATE TABLE dbo.Cars ( CarID INT NOT NULL, Attributes NVARCHAR(4000), CONSTRAINT PK_Car PRIMARY KEY(CarID), CONSTRAINT IsValidJSON CHECK (ISJSON(Attributes) = 1) );
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
- Download the latest SQL Server 2016 CTP (or register for a trial of Azure SQL Database, where this feature will also be available).
- Make sure your database compatibility level is 130; during the CTP period at least, JSON features may not function under older compatibility levels.
- Try out JSON in scenarios where it may seem useful.
- See these related tips and other resources:
- Advanced JSON Techniques in SQL Server 2016 - Part 1
- Advanced JSON Techniques in SQL Server 2016 - Part 2
- JSON_QUERY (Transact-SQL) (MSDN)
- Compare JSON_VALUE and JSON_QUERY (MSDN)
- Remove square brackets from JSON output with the WITHOUT_ARRAY_WRAPPER Option (MSDN)
- Introducing JSON for SQL Server 2016
- JSON Support in SQL Server 2016
- SQL Server Database Engine Blog
About the author

View all my tips