1

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?

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.