Reading JSON string with Nested elements | SQL Server 2016 –_H5之家

In my [previous post] I discussed about how to Import or Read a JSON string and 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 nested elements, 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 OrderID CustomerID ProductID UnitPrice
100 2000 2000 350

But, if you have more than one nested elements the 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 OrderID CustomerID ProductID UnitPrice
100 2000 NULL NULL

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 OrderID CustomerID ProductID1 UnitPrice1 ProductID2 UnitPrice2
100 2000 2000 350 3000 450

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,
UnitPrice2
FROM 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 OrderID CustomerID ProductID1 UnitPrice1 ProductID2 UnitPrice2
100 2000 2000 350 3000 450

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 UnitPrice
FROM OPENJSON (@json, '$.OrderHeader') as c
CROSS APPLY OPENJSON (c.value, '$.OrderDetail') as p OrderID CustomerID ProductID UnitPrice
100 2000 2000 350
100 2000 3000 450
100 2000 4000 550

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.

本文数据库（mssql）相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库万方数据库

分页：12

转载请注明
本文标题：Reading JSON string with Nested elements | SQL Server 2016 – Part 3
本站链接：
分享请点击：