Advanced JSON Techniques in SQL Server - Part 3

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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



Comments For This Article