Azure SQL中JSON数组列的精确匹配查询及性能优化咨询

核心结论先行 #

• 最简便的查询：用JSON_EXISTS直接判断数组中是否存在匹配的code，无需展开数组。
• 性能最优的查询：用OPENJSON + CROSS APPLY配合JSON索引，只要索引配置合理，CROSS APPLY的性能完全不用担心。

列类型选择纠正 #

你之前的误解需要澄清：Azure SQL中，无论列定义为NVARCHAR(MAX)（建议添加ISJSON约束保证数据合法性）还是所谓的"JSON类型"（本质是带约束的NVARCHAR(MAX)），都可以正常使用OPENJSON，不存在无法使用的情况。

1. 最简便的查询方式 #

适用场景：只需判断行是否包含目标code，无需提取JSON数组中的具体元素 #

保持你现有的直接数组结构（或嵌套对象结构均可，仅路径调整），使用JSON_EXISTS函数直接通过JSON路径查询，写法极简：

直接数组结构（你的原始格式）

SELECT *
FROM YourTable
WHERE JSON_EXISTS(YourJsonColumn, '$[*]?(@.code == "A1")')

嵌套对象结构（如{"mydata": [...]}）

只需调整JSON路径：

SELECT *
FROM YourTable
WHERE JSON_EXISTS(YourJsonColumn, '$.mydata[*]?(@.code == "A1")')

2. 性能最优的查询方式 #

适用场景：需频繁按code查询，或需要提取匹配的JSON元素内容 #

使用OPENJSON + CROSS APPLY展开数组，并配合JSON索引优化性能。只要索引配置正确，CROSS APPLY的性能表现远优于无索引的模糊查询，完全不需要担心性能问题。

查询语句（直接数组结构） #

SELECT t.*
FROM YourTable t
CROSS APPLY OPENJSON(t.YourJsonColumn) WITH (code VARCHAR(10) '$.code') j
WHERE j.code = 'A1'

配套索引创建 #

为了让查询优化器高效过滤数据，创建针对JSON列的非聚集索引：

CREATE NONCLUSTERED INDEX IX_YourTable_JsonCode
ON YourTable (YourJsonColumn)
INCLUDE (Id, Column1, Column2) -- 替换为你查询需要返回的非JSON列
WHERE ISJSON(YourJsonColumn) = 1

如果需要更精准的索引，也可以创建基于JSON表达式的计算列并索引，但上述索引已能覆盖绝大多数场景。

结构选择建议 #

直接数组和嵌套对象结构对查询的简便性、性能差异极小，完全可以根据业务需求选择：

• 若业务逻辑中JSON本身就是独立数组，保持原始结构即可；
• 若需要扩展JSON的其他属性（比如添加元数据），则选择嵌套对象结构。

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