在 Azure SQL DB 中查詢 JSON 以獲取每行一個數組元素

我在 SQL Azure DB 實例中有一個儲存過程，輸入參數是一個 JSON 字元串，格式如下：

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

我想解析這個，以便我可以將記錄插入到這樣的表中：

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

到目前為止，我已經能夠從 JSON 中獲取數據，但不是我想要的格式。這是一個簡單的例子。

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

這是另一個（與簡單化相反）的例子。

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

有沒有辦法通過在 Azure SQL DB 中使用 JSON 工具來獲取每個數組元素的一行？

好吧，我想出了一個答案，可以給我想要的結果，但它看起來很糟糕，我認為它效率低下。生成的計劃的估計子樹成本是 976507。這是查詢。

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

這是結果。

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

引用自：https://dba.stackexchange.com/questions/205474