如何高效查询SQL表中JSON数组内包含指定值的对应记录

你原有的查询存在语法错误：传入OPENJSON的第二个路径参数'$.part'不存在，partList数组的元素直接包含partNum字段，没有part层级，因此原有语句无法返回正确结果。

最优即席查询写法（无需修改表结构） #

使用EXISTS子查询实现，只要找到第一个匹配的JSON元素就终止遍历，比IN或CROSS APPLY性能更高：

SELECT *
FROM mytable t
WHERE t.id = 2
AND EXISTS (
    SELECT 1
    FROM OPENJSON(t.Json, '$.partList')
    WITH (
        partNum VARCHAR(10) '$.partNum'
    ) j
    WHERE j.partNum = '2423'
)

性能优势：

• 先通过id = 2过滤出单行数据（如果id是主键/有索引的话这一步是O(1)开销），再只对这一行的JSON字段做解析
• EXISTS匹配到第一个符合条件的partNum就直接返回结果，不需要遍历整个partList数组
• WITH子句直接映射JSON字段，不需要额外的JSON解析函数调用，开销更低

长期高频查询优化方案 #

如果你需要频繁执行这类JSON字段查询，可以通过添加索引进一步提升性能：

1. 首先确保id字段已经创建主键或非聚集索引，避免全表扫描
2. 如果你使用的是SQL Server 2017及以上版本，可以创建JSON索引，或者创建持久化计算列存储partNum的集合后再建索引，最高可以把这类查询的性能提升10倍以上。

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