A lot of discussions and most important, a feature requests in the Microsoft connect site here with more than 1000 votes is the origin of this new feature in SQL Server 2016.
A lot of NoSQL have already this function and PostgreSQL for example have the json_extract_path_text functionality and you can at every time ask my colleague Daniel Westermann one of our expert in PostgreSQL of this subject.
First, Json is represented by a NVARCHAR for several reasons like Migration (Json format is already stored as string in databases) or Cross feature compatibility (nvarchar is present in all sql server components).
FOR JSON Clause
Like the FOR XML clause, you have the FOR JSON clause and you can use it very easily in a SELECT query like this:
PATH option is used to define JSON paths for each generated property.
I put a “Product” object for the name and the color and I have a new query like this:
You notice that I use 2 types of writing for the Product object:
- [Product.Name]
- ‘Product.Color’
Like FOR XML, the AUTO option exists and generates automatically simple hierarchies:
And if I put the “Product” object like before, you can see that you haven’t the Product hierarchy:
The result is based on the order and names of columns from the table.
Another option is ROOT like already FOR XML to specified a top-level object like for my example Product:
The last and very cool option is INCLUDE_NULL_VALUES.
By default, the JSON clause does not include JSON properties for NULL values in the results but you can use this option to have it:
More information about FOR JSON clause on the msdn link here.
OPENJSON option
OPENJSON option is table-value function (TVF) that accepts a string with a JSON format who can be parsed to put in a table all values like this form:
DECLARE @product NVARCHAR(2000);
SET @product= N'{"Product": [{"EnglishProductName":"Adjustable Race","Color":"NA","Status":"Current"},{"EnglishProductName":"Bearing Ball","Color":"NA","Status":"Current"},{"EnglishProductName":"BB Ball Bearing","Color":"NA","Status":"Current"}]}';
SELECT EnglishProductName, Color, Status FROM OPENJSON(@product, '$.Product')
WITH (
EnglishProductName varchar(50),
Color varchar(15),
Status varchar(7)
) AS Product;
But this option is not valid in the CPT2 and will be in the next release like other news functions ISJSON and JSON_VALUE.
I write another blog of this subject when these functions come out…