How to Parse JSON Column When Hierarchy Key Changes Every Row SQL Server

November 7, 2022

I have a requirement to build an automated report from a SQL table that has a column with nested JSON data. There is a primary key called distribution orders and then another hierarchy where the key is an order number that changes every row. The value for this key is an array of key-value pairs. I am trying to extract the keys of this array as columns and their respective values. I am trying to use OPENJSON and CROSS APPLY to achieve this. I have found plenty of material on how to do this, but the problem I am facing is trying to get by the second layer key that changes every row. After performing CROSS APPLY OPENJSON twice the key is now this order number string which will be different for every row of data that I select. I cannot hardcode this value as I have hundreds of rows to parse. Here is example JSON data to illustrate my problem:

{"distributionOrders":{"3000283984":[{"orderNumber":"3000283984","orderType":"STC","itemNumber":"W01874"}]}}
{"distributionOrders":{"3000308956":[{"orderNumber":"3000308956","orderType":"EVA","itemNumber":"S28741"}]}}
{"distributionOrders":{"3000308961":[{"orderNumber":"3000308961","orderType":"EXP","itemNumber":"W09234"}]}}
{"distributionOrders":{"3000309119":[{"orderNumber":"3000309119","orderType":"STC","itemNumber":"W01874"}]}}

I am trying to get to orderNumber, orderType, and itemNumber. In the first example "3000283984" is the key I am trying to get past without using the key name as it is currently named.

This query works great for one row of data:

SELECT p.orderNumber, p.orderType, p.itemNumber
FROM myDatabase
CROSS APPLY OPENJSON(shipment_details)
WITH (distributionOrders NVARCHAR(max) AS JSON) do
CROSS APPLY OPENJSON(do.distributionOrders)
WITH ("3000325050" NVARCHAR(max) AS JSON)nu
OUTER APPLY OPENJSON(nu."3000325050")
WITH(orderNumber varchar(20), orderType varchar(20), itemNumber varchar(20))p

Now any ideas on how I can get it to scale for hundreds of rows? Modification of the original JSON to be a generic key name may be possible, but is not something I have control over. Thanks!

>Solution :

With no expected results this is a bit of a guess but perhaps this is what you are after?

CREATE TABLE dbo.YourTable (YourJSON nvarchar(MAX));
GO
INSERT INTO dbo.YourTable(YourJSON)
VALUES
(N'{"distributionOrders":{"3000283984":[{"orderNumber":"3000283984","orderType":"STC","itemNumber":"W01874"}]}}'),
(N'{"distributionOrders":{"3000308956":[{"orderNumber":"3000308956","orderType":"EVA","itemNumber":"S28741"}]}}'),
(N'{"distributionOrders":{"3000308961":[{"orderNumber":"3000308961","orderType":"EXP","itemNumber":"W09234"}]}}'),
(N'{"distributionOrders":{"3000309119":[{"orderNumber":"3000309119","orderType":"STC","itemNumber":"W01874"}]}}');
GO

SELECT dO.orderNumber,
       dO.orderType,
       dO.itemNumber
FROM dbo.YourTable YT
     CROSS APPLY OPENJSON(YT.YourJSON, '$.distributionOrders') J
     CROSS APPLY OPENJSON(J.[value])
                 WITH (orderNumber bigint,
                       orderType varchar(3),
                       itemNumber varchar(6)) dO;


GO
DROP TABLE dbo.YourTable;

db<>fiddle