Transforming JSON data and child objects into rows within MSSQL

Question

My very first question after using this site for my own learning! Still a beginner so go easy on me :)

I am trying to format JSON data within MSSQL Server. I have a static JSON file which I can get to display via OPENROWSET, and populate a variable. This JSON file has a "header" and then one or more "child" rows, basically an order header and order detail lines. I can successfully separately display the header's columns as a table in a result-set. I'd like to do the same with just the detail lines - the aim being to then store the header in a table and it's details in a table within SQL server - this part I'll have no issue with.

Here is some mock-up JSON data that I'm working with. This is the exact format I need to use, so I don't have any room to manoeuvre with it but I've populated it with test data:

{
  "InputParameters" : {
    "P_IN_ORDER_SOURCE" : "The_Web",
    "P_IN_ORIG_SYS_DOCUMENT_REF" : "Order666",
    "P_IN_SOLD_TO_CUST_NUMBER" : "JOEB11",
    "P_IN_CUST_ORDER_NUMBER" : "JoeB5556667",
    "P_IN_REQUEST_DATE" : "2021-01-20 08:10:06",
    "P_IN_ORDER_ENTRY_DATE" : "2021-01-20 08:10:06",
    "P_IN_SHIPTO_NAME" : "The Testing Co.",
    "P_IN_SHIPTO_ADDR" : "82 Annweir Crescent",
    "P_IN_SHIPTO_ADDR_2" : null,
    "P_IN_SHIPTO_CITY" : "Atlantis",
    "P_IN_SHIPTO_STATE" : "WSX",
    "P_IN_SHIPTO_ZIP" : "AT55 666",
    "P_IN_SHIPTO_COUNTRY" : "GB",
    "P_IN_OPERATION_CODE" : "CREATE",
    "P_IN_BOOKED_FLAG" : "N",
    "P_IN_OU_NAME" : "ATL UK OU",
    "P_IN_SPECIAL_INSTRUCTIONS" : null,
    "P_IN_QUOTE_NUMBER" : null,
    "P_IN_PRICELIST_ID" : "8",
    "P_IN_EMAIL" : "[email protected]",
    "P_IN_SHIPTO_COUNTY" : null,
    "P_IN_SHIPPING_METHOD" : "Pre 930",
    "P_IN_SHIPPING_INSTRUCTIONS" : null,
    "P_IN_ATTENTION_TO" : "Joe Bloggs",
    "P_IN_FREIGHT_CARRIER_CODE" : null,
    "P_IN_IS_RETURN" : null,
    "P_IN_SALES_REP" : null,
    "P_IN_LINE_DATA" : [ {
      "P_IN_LINE_DATA_ITEM" : [ {
        "ORIG_SYS_DOCUMENT_REF" : "Order666",
        "ORIG_SYS_LINE_REF" : "1",
        "CUSTOMER_LINE_NUMBER" : "1",
        "ITEM_TYPE_CODE" : "STANDARD",
        "ITEM_DESCRIPTION" : "SKU7776",
        "USER_ITEM_DESCRIPTION" : "SKU7776",
        "TOP_MODEL_LINE_REF" : null,
        "LINK_TO_LINE_REF" : null,
        "COMPONENT_CODE" : null,
        "ORDERED_QUANTITY" : "6",
        "ORDER_QUANTITY_UOM" : null,
        "UNIT_LIST_PRICE" : "16.95",
        "UNIT_SELLING_PRICE" : "16.95",
        "CALCULATE_PRICE_FLAG" : "N",
        "OPERATION_CODE" : "INSERT"
      }, {
        "ORIG_SYS_DOCUMENT_REF" : "Order666",
        "ORIG_SYS_LINE_REF" : "2",
        "CUSTOMER_LINE_NUMBER" : "2",
        "ITEM_TYPE_CODE" : "STANDARD",
        "ITEM_DESCRIPTION" : "SKU12345",
        "USER_ITEM_DESCRIPTION" : "SKU12345",
        "TOP_MODEL_LINE_REF" : null,
        "LINK_TO_LINE_REF" : null,
        "COMPONENT_CODE" : null,
        "ORDERED_QUANTITY" : "6",
        "ORDER_QUANTITY_UOM" : null,
        "UNIT_LIST_PRICE" : "11.89",
        "UNIT_SELLING_PRICE" : "11.89",
        "CALCULATE_PRICE_FLAG" : "N",
        "OPERATION_CODE" : "INSERT"
      }, {
        "ORIG_SYS_DOCUMENT_REF" : "Order666",
        "ORIG_SYS_LINE_REF" : "3",
        "CUSTOMER_LINE_NUMBER" : "3",
        "ITEM_TYPE_CODE" : "STANDARD",
        "ITEM_DESCRIPTION" : "SKU9999",
        "USER_ITEM_DESCRIPTION" : "SKU9999",
        "TOP_MODEL_LINE_REF" : null,
        "LINK_TO_LINE_REF" : null,
        "COMPONENT_CODE" : null,
        "ORDERED_QUANTITY" : "8",
        "ORDER_QUANTITY_UOM" : null,
        "UNIT_LIST_PRICE" : "46.42",
        "UNIT_SELLING_PRICE" : "46.42",
        "CALCULATE_PRICE_FLAG" : "N",
        "OPERATION_CODE" : "INSERT"
      } ]
    } ]
  }
}

I've been trying to learn how to use this JSON with SQL server pretty much starting from today. I've explored the OPENJSON() function which like I've said, I can define the separate columns and path with the header information - but as soon as I try to do similar and path to the detail objects, I just get NULLs back in each column.

Any suggestions at all? Apologies if I've missed any key information out here! Many thanks!

Please edit your question and add your T-SQL

Yitzhak Khabinsky
– Yitzhak Khabinsky

2021-01-22 18:21:57 +00:00
Commented Jan 22, 2021 at 18:21 — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jan 22, 2021 at 18:21

David Browne - Microsoft · Accepted Answer · 2021-01-22 19:01:54Z

Something like this:

declare @json nvarchar(max) = '
{
  "InputParameters" : {
    "P_IN_ORDER_SOURCE" : "The_Web",
    "P_IN_ORIG_SYS_DOCUMENT_REF" : "Order666",
    "P_IN_SOLD_TO_CUST_NUMBER" : "JOEB11",
    "P_IN_CUST_ORDER_NUMBER" : "JoeB5556667",
    "P_IN_REQUEST_DATE" : "2021-01-20 08:10:06",
    "P_IN_ORDER_ENTRY_DATE" : "2021-01-20 08:10:06",
    "P_IN_SHIPTO_NAME" : "The Testing Co.",
    "P_IN_SHIPTO_ADDR" : "82 Annweir Crescent",
    "P_IN_SHIPTO_ADDR_2" : null,
    "P_IN_SHIPTO_CITY" : "Atlantis",
    "P_IN_SHIPTO_STATE" : "WSX",
    "P_IN_SHIPTO_ZIP" : "AT55 666",
    "P_IN_SHIPTO_COUNTRY" : "GB",
    "P_IN_OPERATION_CODE" : "CREATE",
    "P_IN_BOOKED_FLAG" : "N",
    "P_IN_OU_NAME" : "ATL UK OU",
    "P_IN_SPECIAL_INSTRUCTIONS" : null,
    "P_IN_QUOTE_NUMBER" : null,
    "P_IN_PRICELIST_ID" : "8",
    "P_IN_EMAIL" : "[email protected]",
    "P_IN_SHIPTO_COUNTY" : null,
    "P_IN_SHIPPING_METHOD" : "Pre 930",
    "P_IN_SHIPPING_INSTRUCTIONS" : null,
    "P_IN_ATTENTION_TO" : "Joe Bloggs",
    "P_IN_FREIGHT_CARRIER_CODE" : null,
    "P_IN_IS_RETURN" : null,
    "P_IN_SALES_REP" : null,
    "P_IN_LINE_DATA" : [ {
      "P_IN_LINE_DATA_ITEM" : [ {
        "ORIG_SYS_DOCUMENT_REF" : "Order666",
        "ORIG_SYS_LINE_REF" : "1",
        "CUSTOMER_LINE_NUMBER" : "1",
        "ITEM_TYPE_CODE" : "STANDARD",
        "ITEM_DESCRIPTION" : "SKU7776",
        "USER_ITEM_DESCRIPTION" : "SKU7776",
        "TOP_MODEL_LINE_REF" : null,
        "LINK_TO_LINE_REF" : null,
        "COMPONENT_CODE" : null,
        "ORDERED_QUANTITY" : "6",
        "ORDER_QUANTITY_UOM" : null,
        "UNIT_LIST_PRICE" : "16.95",
        "UNIT_SELLING_PRICE" : "16.95",
        "CALCULATE_PRICE_FLAG" : "N",
        "OPERATION_CODE" : "INSERT"
      }, {
        "ORIG_SYS_DOCUMENT_REF" : "Order666",
        "ORIG_SYS_LINE_REF" : "2",
        "CUSTOMER_LINE_NUMBER" : "2",
        "ITEM_TYPE_CODE" : "STANDARD",
        "ITEM_DESCRIPTION" : "SKU12345",
        "USER_ITEM_DESCRIPTION" : "SKU12345",
        "TOP_MODEL_LINE_REF" : null,
        "LINK_TO_LINE_REF" : null,
        "COMPONENT_CODE" : null,
        "ORDERED_QUANTITY" : "6",
        "ORDER_QUANTITY_UOM" : null,
        "UNIT_LIST_PRICE" : "11.89",
        "UNIT_SELLING_PRICE" : "11.89",
        "CALCULATE_PRICE_FLAG" : "N",
        "OPERATION_CODE" : "INSERT"
      }, {
        "ORIG_SYS_DOCUMENT_REF" : "Order666",
        "ORIG_SYS_LINE_REF" : "3",
        "CUSTOMER_LINE_NUMBER" : "3",
        "ITEM_TYPE_CODE" : "STANDARD",
        "ITEM_DESCRIPTION" : "SKU9999",
        "USER_ITEM_DESCRIPTION" : "SKU9999",
        "TOP_MODEL_LINE_REF" : null,
        "LINK_TO_LINE_REF" : null,
        "COMPONENT_CODE" : null,
        "ORDERED_QUANTITY" : "8",
        "ORDER_QUANTITY_UOM" : null,
        "UNIT_LIST_PRICE" : "46.42",
        "UNIT_SELLING_PRICE" : "46.42",
        "CALCULATE_PRICE_FLAG" : "N",
        "OPERATION_CODE" : "INSERT"
      } ]
    } ]
  }
}
'

select *
from openjson(@json,'$.InputParameters.P_IN_LINE_DATA[0].P_IN_LINE_DATA_ITEM')
with
(
  ORIG_SYS_DOCUMENT_REF varchar(200),
  ORIG_SYS_LINE_REF int,
  CUSTOMER_LINE_NUMBER int,
  ITEM_TYPE_CODE varchar(200),
  -- . . .
  OPERATION_CODE varchar(200)
)

Thank you David. This looks very similar to what I had - except I’m pulling in the data via OPENROWSET and pointing to the file, then filling a variable with it. I’ll grab the SQL I was trying tomorrow - I did miss out the key index you have in yours [0]. Will see how I get on.

Collectives™ on Stack Overflow

Transforming JSON data and child objects into rows within MSSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related