Azure SQL DB – How to Query JSON to Get One Array Element Per Row

azure-sql-databasejsont-sql

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?

Best Answer

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
Related Question