SQL Server按条件解析JSON的查询性能优化咨询

原查询的性能缺陷 #

• 两次调用OPENJSON重复解析同一段JSON数组，产生不必要的解析开销
• 使用LIKE '%1A%'模糊匹配整个JSON对象字符串，匹配效率极低，还存在误判风险：如果数组元素的其他属性值刚好包含1A字符串，会被错误过滤
• 冗余子查询进一步提升了逻辑开销，数据量越大性能下降越明显

优化后的查询代码 #

declare @json nvarchar(max)

set @json = '{
   "testJson":{
      "testID":"Test1",
      "Value":[
         {
            "Value1":"",
            "Value2":"",
            "Value3":"",
            "Type": "1A"
         },
         {
            "Value1":"123",
            "Value2":"456",         
            "Value3":"Automatic",
            "Type": "2A"
         },
         {
            "Value1":"789",
            "Value2":"159",         
            "Value3":"Manual",
            "Value4":"Success"  ,
            "Type": "3A"
         }
      ]
   }
}'

select 
    'ValueFields' as groupDef,
    STRING_AGG(c.value, ' | ') as val
from 
    openjson(@json, '$.testJson.Value') 
    with (
        Type varchar(10) '$.Type', -- 结构化提取元素的Type属性
        ItemJson nvarchar(max) '$' as JSON -- 保留当前元素完整JSON用于后续属性遍历
    ) as b
cross apply 
    openjson(b.ItemJson) as c
where 
    b.Type <> '1A' -- 直接等值过滤，无模糊匹配、无冗余子查询

优化效果说明 #

• 仅需解析一次JSON数组，通过OPENJSON的WITH子句直接结构化提取所需属性，避免重复解析开销
• 等值过滤Type <> '1A'比原模糊匹配性能提升数倍，且过滤逻辑完全准确，不会出现误判
• 去掉冗余子查询后执行计划更简洁，大体积JSON的处理效率提升尤为明显

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