January 25, 2018 at 2:09 am
I'm currently working my way through Itzik's book for 70-761 and I'm struggling a bit with the JSON section.
There are fairly basic examples for extracting JSON but I'm busy playing and wanted something that I believe is a little more realistic and I can't find a solution out there.
Basically, I have JSON data that is an array of Customer, where each customer has an array of Order:
DECLARE @json AS NVARCHAR(MAX) = N'
[
{
"Customer":{
"Id":1,
"Name":"Customer NRZBB",
"Order":[{
"Id":10692,
"Date":"2015-10-03",
"Delivery":null
}]
}
},
{
"Customer":{
"Id":2,
"Name":"Customer NRZCC",
"Order":[{
"Id":10703,
"Date":"2015-10-13",
"Delivery":null
},
{
"Id":10704,
"Date":"2015-10-14",
"Delivery":null
}]
}
}
]';
What I would like to get is a row for each order, with Customer Name, Order ID and Order Date - so three rows in total.
I haven't found an example of extracting from an array within an array and my attempts aren't getting me anywhere useful.
Extracting just the Customer data is easy, with:SELECT ID ,
[Name]
FROM OPENJSON(@json)
WITH (ID INT '$.Customer.Id',
[Name] VARCHAR(50) '$.Customer.Name');
GO
However, my fun begins when I attempt to also extract the order details for each customer.
Any assistance would be appreciated.
January 25, 2018 at 4:06 am
Quick suggestion, use JSON_QUERY to extract the Order array, here is an example
😎
DECLARE @json AS NVARCHAR(MAX) = N'
[
{
"Customer":{
"Id":1,
"Name":"Customer NRZBB",
"Order":[{
"Id":10692,
"Date":"2015-10-03",
"Delivery":null
}]
}
},
{
"Customer":{
"Id":2,
"Name":"Customer NRZCC",
"Order":[{
"Id":10703,
"Date":"2015-10-13",
"Delivery":null
},
{
"Id":10704,
"Date":"2015-10-14",
"Delivery":null
}]
}
}
]';
SELECT
JSON_VALUE(OJC.[value],'$.Customer.Id') AS CUSTOMER_ID
,JSON_VALUE(OJC.[value],'$.Customer.Name') AS CUSTOMER_NAME
,JSON_VALUE(OCV.[value],'$.Id') AS ORDER_ID
,JSON_VALUE(OCV.[value],'$.Date') AS ORDER_DATE
,JSON_VALUE(OCV.[value],'$.Delivery') AS ORDER_DELIVERY
FROM OPENJSON(@json) OJC
CROSS APPLY OPENJSON(JSON_QUERY(OJC.[value]),'$.Customer.Order') OCV;
Output
CUSTOMER_ID CUSTOMER_NAME ORDER_ID ORDER_DATE ORDER_DELIVERY
------------ --------------- --------- ----------- ---------------
1 Customer NRZBB 10692 2015-10-03 NULL
2 Customer NRZCC 10703 2015-10-13 NULL
2 Customer NRZCC 10704 2015-10-14 NULL
January 25, 2018 at 4:22 am
BrainDonor - Thursday, January 25, 2018 4:18 AMWonderful, thank you very much.
I had tried a CROSS JOIN but hadn't nested the JSON_QUERY inside the OPENJSON. I would never have thought of that.
Thanks for your time.
You are welcome Steve.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply