Advanced JSON Techniques in SQL Server 2016 - Part 3


By:   |   Updated: 2016-01-07   |   Comments   |   Related: 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


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, SQLPerformance.com, and also blogs at sqlblog.org.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

New Drop If Exists Syntax in SQL Server 2016

New CREATE OR ALTER statement in SQL Server 2016 SP1

Automatic Updates for SQL Server Management Studio

SQL Server 2016 Always Encrypted

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