Shredding JSON Array within Array

  • BrainDonor

    SSCoach

    Points: 19182

    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.

    Steve Hall
    Linkedin
    Blog Site

  • Eirikur Eiriksson

    SSC Guru

    Points: 182343

    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

  • BrainDonor

    SSCoach

    Points: 19182

    Wonderful, 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.

    Steve Hall
    Linkedin
    Blog Site

  • Eirikur Eiriksson

    SSC Guru

    Points: 182343

    BrainDonor - Thursday, January 25, 2018 4:18 AM

    Wonderful, 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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply