Reading JSON string with Nested elements | SQL Server 2016 – Part 3

来源:转载

In my [previous post] I discussed about how to Import or Read a JSON stringand convert it in relational/tabular format in row/column from.

Today in this post I’ll talk about how to read/parse JSON string with nestedelements, just like XML.

Native JSONsupport in SQL Server 2016 provides you few functions to read and parse your JSON string into relational format and these are:

– OPENJSON()Table valued function: parses JSON text and returns rowset view of JSON.

– JSON_Value()Scalar function: returns a value from JSON on the specified path.

We will see usage of both the functions in our example below:

Here, we have just one nested element, and the OPENJSON() function will get you the child elements values.

–> Method #1.a. Using OPENJSON() function: DECLARE @json NVARCHAR(1000)SELECT @json = N'{ "OrderHeader": [{ "OrderID": 100, "CustomerID": 2000, "OrderDetail": { "ProductID": 2000, "UnitPrice": 350 }} ]}'SELECT OrderID, CustomerID, [OrderDetail.ProductID] AS ProductID, [OrderDetail.UnitPrice] AS UnitPrice FROM OPENJSON (@json, '$.OrderHeader') WITH (OrderID INT, CustomerID INT, [OrderDetail.ProductID] INT, [OrderDetail.UnitPrice] INT) AS Orders OrderIDCustomerIDProductIDUnitPrice10020002000350

But, if you have more than one nested elementsthe same query will give just 1 row with NULL values under the child columns, like this.

–> Method #1.b. In case of multiple child elements: DECLARE @json NVARCHAR(1000)SELECT @json = N'{ "OrderHeader": [ { "OrderID": 100, "CustomerID": 2000, "OrderDetail": [ {"ProductID": 2000, "UnitPrice": 350 }, { "ProductID": 3000,"UnitPrice": 450 } ] } ]}'SELECT OrderID, CustomerID, [OrderDetail.ProductID] AS ProductID, [OrderDetail.UnitPrice] AS UnitPrice FROM OPENJSON (@json, '$.OrderHeader') WITH (OrderID INT, CustomerID INT, [OrderDetail.ProductID] INT, [OrderDetail.UnitPrice] INT) AS Orders OrderIDCustomerIDProductIDUnitPrice1002000NULLNULL

You might be expecting 2 rows with same OrderID & CustomerID, with different ProductID & UnitPrice, right?

Because, now you are having array of child elements with OrderDetail node (notice the square-bracket after “OrderDetail”:node), thus the Query is not able to find the key on the path.

In this case what you have to do is, use the array positions with square brackets (“[” and “]”) in your query and call out separate columns for each child element, like below:

DECLARE @json NVARCHAR(1000)SELECT @json = N'{ "OrderHeader": [ { "OrderID": 100, "CustomerID": 2000, "OrderDetail": [ {"ProductID": 2000, "UnitPrice": 350 }, { "ProductID": 3000,"UnitPrice": 450 } ] } ]}'SELECT OrderID, CustomerID, [OrderDetail[0]].ProductID] AS ProductID1, [OrderDetail[0]].UnitPrice] AS UnitPrice1, [OrderDetail[1]].ProductID] AS ProductID2, [OrderDetail[1]].UnitPrice] AS UnitPrice2 FROM OPENJSON (@json, '$.OrderHeader') WITH (OrderID INT, CustomerID INT, [OrderDetail[0]].ProductID] INT, [OrderDetail[0]].UnitPrice] INT, [OrderDetail[1]].ProductID] INT, [OrderDetail[1]].UnitPrice] INT ) AS Orders OrderIDCustomerIDProductID1UnitPrice1ProductID2UnitPrice2100200020003503000450

You can also specify the child elements with full path by using the dollar sign “$” inside the WITH() clause (instead at column level above), like below:

–> Method #2. Using OPENJSON() function: DECLARE @json NVARCHAR(1000)SELECT @json = N'{ "OrderHeader": [ { "OrderID": 100, "CustomerID": 2000, "OrderDetail": [ {"ProductID": 2000, "UnitPrice": 350 }, { "ProductID": 3000,"UnitPrice": 450 } ] } ]}'SELECT OrderID, CustomerID, ProductID1, UnitPrice1, ProductID2, UnitPrice2FROM OPENJSON (@json, '$.OrderHeader')WITH (OrderID INT '$.OrderID',CustomerID INT '$.CustomerID',ProductID1 INT '$.OrderDetail[0].ProductID',UnitPrice1 INT '$.OrderDetail[0].UnitPrice', ProductID2 INT '$.OrderDetail[1].ProductID',UnitPrice2 INT '$.OrderDetail[1].UnitPrice' ) AS Orders OrderIDCustomerIDProductID1UnitPrice1ProductID2UnitPrice2100200020003503000450

Ok, so by using the key path and the array position we can get the child elements value in our Query result-set by using above 2 methods.

But instead of having them in separate columns how about pulling them in separate rows, this will also make your query dynamic as you would not know the number of child-elements before hand, right?

This can be done by CROSS APPLYing the JSON child node with the parent node and using the JSON_Value() function, like shown below:

–> Method #3. Using JSON_Value() with OPENJSON() function: DECLARE @json NVARCHAR(1000)SELECT @json = N'{ "OrderHeader": [ { "OrderID": 100, "CustomerID": 2000, "OrderDetail": [ {"ProductID": 2000, "UnitPrice": 350 }, { "ProductID": 3000,"UnitPrice": 450 },{ "ProductID": 4000,"UnitPrice": 550 } ] } ]}'SELECT JSON_Value (c.value, '$.OrderID') as OrderID, JSON_Value (c.value, '$.CustomerID') as CustomerID, JSON_Value (p.value, '$.ProductID') as ProductID, JSON_Value (p.value, '$.UnitPrice') as UnitPriceFROM OPENJSON (@json, '$.OrderHeader') as cCROSS APPLY OPENJSON (c.value, '$.OrderDetail') as p OrderIDCustomerIDProductIDUnitPrice100200020003501002000300045010020004000550

Ok, that’s it for today.

In my [next post]I’ll talk about storing JSON string in a table and doing some hands-on with it.



分享给朋友:
您可能感兴趣的文章:
随机阅读: