SQL Server中使用JSON_QUERY根据SourceFieldId值筛选JSON对象的实现方法

嗨，我来帮你搞定这个问题！你当前的查询没生效主要有两个关键点：一是JSON里的Mapping是数组类型，直接用$.Mapping.SourceFieldID无法定位到数组元素内的属性；二是要注意大小写匹配——你的JSON字段是SourceFieldId（小写d结尾），但你之前的WHERE子句里写的是SourceFieldID，SQL Server的JSON路径是区分大小写的，这也会导致匹配失败。

下面给你两种可行的解决方法：

方法一：用OPENJSON展开数组后筛选 #

这种方法适合需要把符合条件的JSON对象拆成单独行返回的场景，兼容性也更好：

SELECT 
    JSON_QUERY(mapping_item.value) AS Matching_Mapping_Object
FROM 
    MapHistories
CROSS APPLY 
    OPENJSON(MapJSON, '$.Mapping') AS mapping_item
WHERE 
    JSON_VALUE(mapping_item.value, '$.SourceFieldId') = 46 -- 替换成你需要匹配的ID值

原理说明： #

• CROSS APPLY OPENJSON(MapJSON, '$.Mapping')会把Mapping数组里的每个元素拆成独立的行
• 用JSON_VALUE(mapping_item.value, '$.SourceFieldId')提取每个数组元素里的SourceFieldId值进行筛选
• 最后用JSON_QUERY把符合条件的数组元素重新转换成JSON对象返回

方法二：用JSON路径直接筛选数组元素（SQL Server 2017+支持） #

如果你希望直接返回包含所有符合条件元素的JSON数组，可以用这种更简洁的写法：

SELECT 
    JSON_QUERY(MapJSON, '$.Mapping[?(@.SourceFieldId == 46)]') AS Matching_Mapping_Objects
FROM 
    MapHistories
WHERE 
    JSON_EXISTS(MapJSON, '$.Mapping[?(@.SourceFieldId == 46)]')

原理说明： #

• $.Mapping[?(@.SourceFieldId == 46)]是JSON筛选路径，意思是找出Mapping数组中SourceFieldId等于46的所有元素
• JSON_EXISTS用来过滤掉那些没有符合条件元素的行，避免返回空的JSON数组

补充提醒： #

如果你只需要匹配单个元素，也可以用索引定位（比如$.Mapping[0].SourceFieldId），但这种方式不够灵活，没法动态筛选所有符合条件的元素，所以更推荐上面两种方法。

备注：内容来源于stack exchange，提问作者AlliDeacon