Reading JSON string with Nested array of elements | SQL Server 2016 – Part 3
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 array of elements, just like XML.
Native JSON support 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?
Instead you get ProductID & UnitPrice column values as NULL. Because, here 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.
Dear Manoj,
I tried your sample, however , there is a problem with 1.b case.
When I execute 1.b case the result is null for the array ( OrderID + CustomerID still show value )
Could you please check it again ?
Hi @Le, that’s expected, OrderID & CustomerID will have values, but the rest two columns ProductID & UnitPrice will be NULL.
~Manoj
Dear Manoj,
I am using Method#3, json_value, my data have 5 key and value rows and out of these 1 block is having multiple values, so I am not able to combine the other as those seems independent, e.g. in your example say we have OrderHeader which has 3 values then we have another element at same level as OrderHeader say, its Sale2017 and it only have 1 block , something like:
DECLARE @json NVARCHAR(1000)
SELECT @json =
N'{
“OrderHeader”: [
{
“OrderID”: 100,
“CustomerID”: 2000,
],
“Sale2017”:{
“TotalUnit”:100000,
“GrossAmount”:200000
}
}’
Then how we can handle this, thanks in advance for your kind help.
I figured out the solution, thanks.
HI Manoj, i am using your blog to retrieve values from a json and its not working for reason.. could you please help me .. thank you. really do appreciate your help:
declare @var nvarchar(max)
set @var='{
“StudentId”: “88”,
“locations”: [
{
“city”: “New York”,
“state”: “NY”,
“zip”: “10001”
},
{
“city”: “Edison”,
“state”: “NJ”,
“zip”: “08820”
}
]
}’
FROM OPENJSON (@var, ‘$’) as c
CROSS APPLY OPENJSON (c.value, ‘$.locations’) as p
Hi Sravu,
Try this code:
SELECT
JSON_Value (@var, ‘$.StudentId’) as studentid,
JSON_Value (c.value, ‘$.city’) as city,
JSON_Value (c.value, ‘$.state’) as state,
JSON_Value (c.value, ‘$.zip’) as zip
FROM OPENJSON (@var,’$.locations’) as c
There is no need to chunk it based on studentid as that is by default a separator here so you can extract that directly from the json string.
Hope it helps!
Thanks
Hi Manoj,
I am trying to extract values from an array in a JSON. I have loaded these JSON as records in a table. Is there a way to make OPENJSON work on every json record in the table in one single query? Or, should I write a cursor to extract values record by record (Or Json by Json)
Thanks a lot… Very informative…
Hi Manoj, i have a need to work on a json like this :
{
“ProposalNumber”: 0,
“ProposalName”: “test”,
“SelectedProductLines”: [3,4],
“SelectedCustomerLocations”: [3,4],
“SelectedEndUserLocations”: [3,4],
“ProposalCreatedOnDate”: “2018-07-01T18:30:00Z”
}
Here, i want to filter the records (OR operation) on these optional parameters, i want to build a query like Select * from Proposal where ProductLines IN(‘3′,’4’) .
In this case how will i fetch the values from SelectedProductLines array in this passed json string ?
Any help is appreciated
Thanks.
Hi Manoj,
What about this I’ve try with cross apply but I can’t see the Pages_visited and erros columns, it seems the it can’t see multiple sections
{“state”:”GA”,”email”:null,”ip_address”:”114.63.187.46″,”client_browser”:”mozilla”,”feedback”:null,
“session”:{“pages_visited”:[“links”,”about”,”support”],
“date”:”07/28/2017″,
“time”:”17:10″,
“errors”:[400,404]}}’
Thanks
Oded Dror
Hi Manoj,
The post was very helpful. However I have run into problem reading “tags” arrarylist in the output below. Could you please help? Thanks
DECLARE @json NVARCHAR(MAX)
SET @json =
N'{
“sessions”: [
{
“id”: 305,
“title”: “Election Campaign”,
“speakers”: [
{
“id”: 315,
“name”: “Vijaykanth”
}
],
“tags”: [
“politics”,
“travel”
],
“isDeleted”: false,
“imageUrl”: “https://s3-eu-west-1.amazonaws.com/”
}
]
}’
select
JSON_VALUE(sess.value, ‘$.id’) as sessionId,
JSON_VALUE(sess.value, ‘$.title’) as title,
JSON_VALUE(s.value, ‘$.id’) as speakerid
from openjson(@json,’$.sessions’) as sess
CROSS APPLY OPENJSON (sess.value, ‘$.speakers’) as s
Jaideep, what output you are expecting from your query?
Do you want tags in single column comma separated or separate columns/rows?
Yes Manoj. Comma separated in a single row. Also would be curious to know if we can split this into 2 rows with values “politics” in one and “travel” in other. Thanks for your help
Data in 2 rows in working but still not getting comma separated values in a single row
DECLARE @json NVARCHAR(MAX)
SET @json =
N'{
“sessions”: [
{
“id”: 305,
“title”: “Election Campaign”,
“speakers”: [
{
“id”: 315,
“name”: “Vijaykanth”
}
],
“tags”: [
“politics”,
“travel”
],
“imageUrl”: “https://s3-eu-west-1.amazonaws.com/”
}
]
}’
select
JSON_VALUE(sess.value, ‘$.id’) as sessionId,
JSON_VALUE(sess.value, ‘$.title’) as title,
JSON_VALUE(s.value, ‘$.id’) as speakerid
,t.value as tags
from openjson(@json,’$.sessions’) as sess
CROSS APPLY OPENJSON (sess.value, ‘$.speakers’) as s
CROSS APPLY OPENJSON (sess.value, ‘$.tags’) as t
HI Manoj
i have a sample json whcih looks like
{
“per_page”: 50,
“total”: 85,
“data”: [
{
stuffs here
}
],
“page”:1,
“links”: {
“self”: “www.abc.com”,
“last”: “www.xyz.com”,
“next”: “www.asg.com”
}
}
i tried using the below it does not work
select json_value (Subs.value, ‘$.links.next’) AS next_link
from openjson(@Responsedetails ,’$.page’) AS Subs
can you please help as to how should i load the http://www.asg.com in a table
Thank you very much method 3 worked for my project God bless you!
Excellent. Super explanation.
Thanks you very much .
Hello Manoj,
I have a complex json data and i want to store it in multiple tables. But all tables are dynamic like i dont know at what time which table caomes.
Hi , can you help me on how to get / display array index values using open_json () . I have a situation where I am having values like [ 2005,2006,2007,2008] I want to see this in a table as 4 records and each record should show array index in first column, array value in 2d column.
Are you looking to try this?
DECLARE @json NVARCHAR(1000) = N'[2005,2006,2007,2008]’
SELECT * FROM OPENJSON (@json)
Hi, Can you pls help me how to read the below data in SQL?
{
“Permission”: [
{
“R”: [
16470,
16490,
16491,
16492,
16493,
16532
]
}
]
}
Check this:
select * from OPENJSON(@str, ‘$.Permission[0].R’)
Hi, please help me in selecting the keys which have value as ‘true’.
In this case I should get only prop1 and prop3
[
{
“prop1”: true,
“prop2”: false,
“prop3”: true
]
Your JSON is not properly formatted, check this:
declare @str varchar(100) = ‘{
“prop1”: true,
“prop2”: false,
“prop3”: true
}’
select *
from OPENJSON(@str, ‘$’)
where value = ‘true’
and what if I have this json in a column
column
{“prop1”: true,“prop2”: false,“prop3”: true}
check this:
drop table if exists #temp1
create table #temp1 (id int, JSONcol varchar(100))
insert into #temp1
select 101 as ID, ‘{“prop1”: true, “prop2”: false, “prop3”: true}’ as JSONcol
select t.id, o.[key], o.[value]
from #temp1 t
cross apply OPENJSON(t.JSONcol, ‘$’) o
where o.value = ‘true’
Thank you very much. That worked.
can u please provide c# code for this.
What exactly do you want to do by C# here?