如何在T-SQL中提取JSON的多层嵌套值？

问题原因解析 #

你遇到的两个问题根源如下：

1. 第一个查询报错是因为(select [description] from #OrderHistory)返回了多条JSON数据，而OPENJSON()单参数模式仅能接收单个JSON字符串，无法处理多行结果。
2. 第二个查询仅解析了JSON的第一层结构，没有深入处理deliverySchedule、details数组和candyLimit这些嵌套层级。

正确查询写法 #

针对你的需求，可通过两种方式提取目标嵌套值：

方式1：直接用JSON_VALUE提取固定路径值 #

适合JSON结构固定、只需提取数组首个元素的场景：

SELECT
    OH.OrderId, -- 替换为#OrderHistory表的实际主键/标识字段
    -- 提取deliverySchedule的@DeliveryType
    JSON_VALUE(OH.[description], '$.deliverySchedule."@DeliveryType"') AS DeliveryType,
    -- 提取details数组第一个元素的@type（JSON数组索引从0开始）
    JSON_VALUE(OH.[description], '$.deliverySchedule.details[0]."@type"') AS DetailType,
    -- 提取details下candyLimit的@type
    JSON_VALUE(OH.[description], '$.deliverySchedule.details[0].candyLimit."@type"') AS CandyLimitType
FROM #OrderHistory OH

注意：带特殊字符（如@）的键名，必须在JSON路径里用双引号包裹；JSON数组的索引从0开始，你之前写的[1]会指向第二个元素，不符合示例JSON的结构。

方式2：用CROSS APPLY逐层展开嵌套结构 #

如果details数组包含多个元素，需要将每个元素展开为单独行时，用这种方式：

SELECT
    OH.OrderId,
    ds.DeliveryType,
    d.DetailType,
    cl.CandyLimitType
FROM #OrderHistory OH
-- 解析deliverySchedule层级，保留details为JSON格式供后续解析
CROSS APPLY OPENJSON(OH.[description], '$.deliverySchedule')
WITH (
    DeliveryType NVARCHAR(100) '"@DeliveryType"',
    details NVARCHAR(MAX) AS JSON
) ds
-- 展开details数组，每个元素生成一行
CROSS APPLY OPENJSON(ds.details)
WITH (
    DetailType NVARCHAR(100) '"@type"',
    candyLimit NVARCHAR(MAX) AS JSON
) d
-- 解析candyLimit层级
CROSS APPLY OPENJSON(d.candyLimit)
WITH (
    CandyLimitType NVARCHAR(100) '"@type"'
) cl

原错误语句修正 #

如果只是测试单条JSON数据，可通过TOP 1限制子查询返回单行：

SELECT * 
FROM OPENJSON((SELECT TOP 1 [description] FROM #OrderHistory),
'$.deliverySchedule.details[0].candyLimit."@type"'
)

但此方式仅适用于单条数据测试，不适合批量提取场景。

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