SQL Server/T-SQL：如何从单个JSON批量提取所有AgencyId

你的问题出在JSON路径定位上：原JSON的根是数组（用[]包裹），而非单个对象，所以$.Transaction无法匹配到任何数据——数组本身没有Transaction属性，只有数组里的每个元素才有。

以下两种方法可以实现一次性获取所有AgencyId：

方法一：直接遍历数组并指定属性路径 #

使用OPENJSON遍历整个数组（路径$），在WITH子句中定义相对于数组每个元素的属性路径：

SELECT *
FROM OPENJSON(@json, '$')
WITH (
    AgencyId int '$.Transaction.Agency.AgencyId'
)

执行后返回结果：

AgencyId
---------
108200
108201

方法二：拆分数组后交叉解析Transaction对象 #

先将数组拆分为单个元素，再通过CROSS APPLY解析每个元素内的Transaction对象，适合结构更复杂的JSON场景：

SELECT t.AgencyId
FROM OPENJSON(@json, '$') AS arr
CROSS APPLY OPENJSON(arr.value, '$.Transaction')
WITH (
    AgencyId int '$.Agency.AgencyId'
) AS t

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