SQL Server JSON列键为标量或对象时的查询及索引优化问询

针对你遇到的问题——无法确定$.Key是标量还是对象，同时要利用已有的vKey虚拟列索引，这里有两种可行的优化方案：

方案一：利用现有vKey索引拆分查询 #

通过UNION ALL将查询拆分为两部分，分别处理标量值和对象/数组的情况，确保第一部分完全命中索引：

-- 第一部分：命中vKey索引，快速筛选标量值的行
SELECT ID, JSON_VALUE(Info, '$.Key') as json_val, JSON_QUERY(Info, '$.Key') as json_query
FROM TABLE
WHERE vKey IS NOT NULL

UNION ALL

-- 第二部分：仅扫描vKey为NULL的行，筛选出存在对象/数组的行
SELECT ID, JSON_VALUE(Info, '$.Key') as json_val, JSON_QUERY(Info, '$.Key') as json_query
FROM TABLE
WHERE vKey IS NULL
  AND JSON_QUERY(Info, '$.Key') IS NOT NULL

这种方式不需要额外创建索引，第一部分查询可以直接利用vKey的索引快速返回标量值的行；第二部分只需要扫描vKey为NULL的子集（而非全表），性能比原查询更优。

方案二：新增计算列索引覆盖所有情况 #

如果允许新增索引，可以创建一个基于JSON_EXISTS的计算列，覆盖$.Key存在的所有场景（标量、对象、数组）：

1. 新增计算列：

ALTER TABLE TABLE
ADD IsKeyExists AS CASE WHEN JSON_EXISTS(Info, '$.Key') THEN 1 ELSE 0 END PERSISTED;

2. 为计算列创建索引：

CREATE NONCLUSTERED INDEX IX_Table_IsKeyExists ON TABLE (IsKeyExists) INCLUDE (ID, Info);

3. 优化后的查询语句：

SELECT ID, JSON_VALUE(Info, '$.Key') as json_val, JSON_QUERY(Info, '$.Key') as json_query
FROM TABLE
WHERE IsKeyExists = 1;

这种方案查询逻辑更简洁，所有符合条件的行都能通过索引快速定位，适合$.Key为对象/数组的行占比不低的场景。

另外，如果你不需要区分标量和对象的返回结果，也可以用COALESCE合并两列的结果，简化输出：

SELECT ID, COALESCE(JSON_VALUE(Info, '$.Key'), JSON_QUERY(Info, '$.Key')) as KeyValue
FROM TABLE
WHERE IsKeyExists = 1;

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