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?