Query JSON in Azure SQL DB to get one array element per row

Question

I have a stored procedure in a SQL Azure DB instance, and the input parameter is a JSON string formatted like this:

{
 "OrderNumber":["OD12034"],
 "Site":["New York"],
 "LineNumber":[1000,2000],
 "ItemNumber":["BX12345","BX12349"],
 "Quantity":[23,15]
}

I would like to parse this so that I can insert records into a table like this:

ArrayKey | ArrayElement
------------|-----------
OrderNumber | OD12034
Site | New York
LineNumber | 1000
LineNumber | 2000
ItemNumber | BX12345
ItemNumber | BX12349
Quantity | 23
Quantity | 15

So far I have been able to get the data out of the JSON, but not in the format I want. Here is one simplistic example.

SELECT * FROM OPENJSON(@JsonParameter)
key |value |type
-----------------|---------------------------|----
OrderNumber |["OD12034"] |4
Site |["New York"] |4
LineNumber |[1000,2000] |4
ItemNumber |["BX12345","BX12349"] |4
Quantity |[23,15] |4

And here is another (opposite of simplistic) example.

SELECT 
 [ON].OrderNumber
 , [S].Site
 , [LN].LineNumber
 , [IN].ItemNumber
 , [Q].Quantity
FROM OPENJSON(@JsonParameter)
 WITH (
 [OrderNumber] NVARCHAR(MAX) '$.OrderNumber' AS JSON
 ,[Site] NVARCHAR(MAX) '$.Site' AS JSON
 ,[LineNumber] NVARCHAR(MAX) '$.LineNumber' AS JSON
 ,[ItemNumber] NVARCHAR(MAX) '$.ItemNumber' AS JSON
 ,[Quantity] NVARCHAR(MAX) '$.Quantity' AS JSON
 ) AS jsonValues
CROSS APPLY OPENJSON(jsonValues.OrderNumber)
 WITH (OrderNumber NVARCHAR(25) '$') AS [ON]
CROSS APPLY OPENJSON(jsonValues.Site)
 WITH (Site NVARCHAR(25) '$') AS [S]
CROSS APPLY OPENJSON(jsonValues.LineNumber)
 WITH (LineNumber INT '$') AS [LN]
CROSS APPLY OPENJSON(jsonValues.ItemNumber)
 WITH (ItemNumber NVARCHAR(31) '$') AS [IN]
CROSS APPLY OPENJSON(jsonValues.Quantity)
 WITH (Quantity INT '$') AS [Q]
OrderNumber |Site |LineNumber |ItemNumber |Quantity
-------------|------------|-----------|--------------|-----------
OD12034 |New York |1000 |BX12345 |23
OD12034 |New York |1000 |BX12345 |15
OD12034 |New York |1000 |BX12349 |23
OD12034 |New York |1000 |BX12349 |15
OD12034 |New York |2000 |BX12345 |23
OD12034 |New York |2000 |BX12345 |15
OD12034 |New York |2000 |BX12349 |23
OD12034 |New York |2000 |BX12349 |15

Is there way to get one row per array element through the use of JSON tools in Azure SQL DB?

Answer 1

Well, I figured out an answer that gives me the result I want, but it looks bad and I assume it is inefficient. The Estimated Subtree Cost of the plan that is generated is 976507. Here is the query.

SELECT DISTINCT
 ArrayValues.ArrayKey
 , ArrayValues.ArrayElement 
FROM 
(
 SELECT 
 [ON].OrderNumber
 , [S].Site
 , [LN].LineNumber
 , [IN].ItemNumber
 , [Q].Quantity
 FROM OPENJSON(@JsonParameter)
 WITH (
 [OrderNumber] NVARCHAR(MAX) '$.OrderNumber' AS JSON
 ,[Site] NVARCHAR(MAX) '$.Site' AS JSON
 ,[LineNumber] NVARCHAR(MAX) '$.LineNumber' AS JSON
 ,[ItemNumber] NVARCHAR(MAX) '$.ItemNumber' AS JSON
 ,[Quantity] NVARCHAR(MAX) '$.Quantity' AS JSON
 ) AS jsonValues
 CROSS APPLY OPENJSON(jsonValues.OrderNumber)
 WITH (OrderNumber NVARCHAR(31) '$') AS [ON]
 CROSS APPLY OPENJSON(jsonValues.Site)
 WITH (Site NVARCHAR(31) '$') AS [S]
 CROSS APPLY OPENJSON(jsonValues.LineNumber)
 WITH (LineNumber NVARCHAR(31) '$') AS [LN]
 CROSS APPLY OPENJSON(jsonValues.ItemNumber)
 WITH (ItemNumber NVARCHAR(31) '$') AS [IN]
 CROSS APPLY OPENJSON(jsonValues.Quantity)
 WITH (Quantity NVARCHAR(31) '$') AS [Q]
) ExpandedJson
UNPIVOT
(
 ArrayElement FOR ArrayKey IN (OrderNumber, Site, LineNumber, ItemNumber, Quantity)
) AS ArrayValues

And here is the result.

ArrayKey |ArrayElement
---------------|-------------
Quantity |23
ItemNumber |BX12345
Site |New York
LineNumber |1000
LineNumber |2000
ItemNumber |BX12349
Quantity |15
OrderNumber |OD12034