如何使用TSQL的OPENJSON命令解析带双层嵌套数组的JSON数据

适配双层数组结构的OPENJSON解析方案 #

你只需要在原有代码基础上多增加一层CROSS APPLY展开外层嵌套数组即可，完整可运行代码如下：

DECLARE @JSONData varchar(MAX) = '
{
  "OrderHeader": [[
    {
      "OrderID": 100,
      "CustomerID": 2000,
      "OrderDetail": 
        {
          "ProductID":2000, 
          "UnitPrice":350      
        }
    }
  ]]
}
'
SELECT
    OrderID, CustomerID, ProductID, UnitPrice
FROM OPENJSON (@JSONData)
WITH (
    OrderHeader nvarchar(MAX) AS JSON
) AS Orders
-- 新增层：先展开外层数组，拿到内层的订单数组
CROSS APPLY OPENJSON(Orders.OrderHeader)
WITH (
    InnerOrderArray nvarchar(MAX) AS JSON
) AS OuterOrderArray
-- 展开内层数组，对应原有单层数组的处理逻辑
CROSS APPLY OPENJSON(OuterOrderArray.InnerOrderArray)
WITH 
(
    OrderID INT, 
    CustomerID INT,
    OrderDetail nvarchar(MAX) AS JSON
) AS OrderDetail
CROSS APPLY OPENJSON(OrderDetail.OrderDetail)
WITH
(
    ProductID INT, 
    UnitPrice INT
) ;

• 改动逻辑：原来的OrderHeader是[{}]结构，现在变成[[{}]]，第一层OPENJSON展开OrderHeader拿到的每一行都是内层的订单数组，把这个内层数组作为JSON再展开一次，就和你原来的单层数组逻辑完全一致。
• 执行结果和你原有单层数组的解析结果完全相同，兼容后续的字段提取逻辑。

内容的提问来源于stack exchange，提问作者user2921359