JSON 转行集(下)— 按路径过滤、按值过滤与两代索引¶

这一章讲什么: 上一篇把 JSON 拆成了行;本篇解决「怎么快速找到我要的 JSON」: 两个新过滤函数(2022 的路径存在性、2025 的按值包含),和两代索引方案—— 2016 年以来的计算列 workaround,与 2025 年的原生 JSON 索引。

1. 顶层全景:四件套的版图¶

  按路径过滤   JSON_PATH_EXISTS()  2022   → 「有没有这个 key?」返 1/0/NULL
  按值过滤     JSON_CONTAINS()     2025   → 「文档里有没有这个值?」返 1/0,NULL

  索引(两代):
  计算列索引(2016–2022 的正道)  JSON_VALUE → PERSISTED 计算列 → 普通索引
  原生 JSON 索引(2025)          CREATE JSON INDEX,FOR 点名路径控制体积

  图说:前两个回答「在不在」,后两个回答「怎么找得快」。

2. 核心原理¶

2.1 JSON_PATH_EXISTS:exist() 的 JSON 版,但脾气更温和¶

解决什么问题: 按「有没有某个路径」筛行——比如「配送数据里缺 DeliveredWhen 的发票」。

怎么做: JSON_PATH_EXISTS(文档, '路径'),返回 1(存在)、0(不存在)、NULL(输入 NULL)¹。书里的实战数字:Invoices 表 70,500 多行,过滤「缺 DeliveredWhen」只剩 84 行²。

脾气(和 XML 的 exist() 对照的三个差异):

非法路径不报错,安静地返 0——「lightweight, error-free」是书里的原话³。
key 名大小写敏感:$.book.ISBN 匹配小写的 isbn 失败返 0⁴。
数组要么给下标要么给 [*]:$.websites.title 返 0(websites 是数组,不写索引找不到),$.websites[0].title 或 $.websites[*].title 返 1——通配符 * 匹配任意数组元素⁵。

为什么有效: 它只做存在性检查,不取值、不解析值——比 JSON_VALUE 便宜,适合「文档结构参差、先确认 key 在不在再处理」的场景⁶。

2.2 JSON_CONTAINS:按值搜文档,2025 年的新家伙¶

解决什么问题: 「文档里有没有出现过这个值」——尤其在数组里(「这个技能列表里有没有 SQL」)。

怎么做: JSON_CONTAINS(target, 搜的值 [, 路径 [, 模式]])。书里的注意点一条接一条⁷:

输入必须是 JSON 类型——nvarchar 列直接传会报错,要 CAST(列 AS JSON)(2025 前的老列都是 nvarchar,所以几乎每个调用都带 CAST);
通配路径:$.skills[*] 扫数组全体,$.* 扫对象根部的所有属性,$.employees[*].skills[*] 穿透嵌套数组;
路径不存在返 NULL(不是 0)——$.Office[*][*] 用在单层数组上就是 NULL,因为 [*][*] 这个结构在文档里根本不存在⁸。

和 JSON_VALUE 的分工(书里专门一小节): JSON_CONTAINS 是内容过滤——「文档里有没有这个值」,最适合数组成员资格检查;JSON_VALUE 是路径过滤——「取出来再比」,适合字段级比较(=、<、LIKE)。两者都在 WHERE 里,解决的是不同问题⁹。

2.3 计算列索引:2016 年来的唯一正道¶

解决什么问题: JSON 列(文本)上不能直接建索引,但查询要按 JSON 里的值过滤。

怎么做,两步(本篇最该背的操作):

-- 第一步:加计算列——JSON_VALUE 取出值,PERSISTED 落盘,CAST 收窄类型
ALTER TABLE Sales.Invoices ADD ConNote AS
  (CAST(JSON_VALUE(ReturnedDeliveryData, '$.Events[0].ConNote') AS varchar(20))) PERSISTED;
-- 第二步:对计算列建普通索引(可带 INCLUDE 做成覆盖索引)
CREATE INDEX IX_Invoices_ConNote ON Sales.Invoices (ConNote);

三个缺一不可的零件:PERSISTED(计算结果物理存储,否则不能索引)、CAST 收窄(JSON_VALUE 默认吐 nvarchar(4000),索引键这么宽又肥又慢,收窄到实际长度)、索引本身(可选 INCLUDE)¹⁰。

效果(有数字): 同一个查询,WHERE ConNote = 'EAN-125-1051'——无索引时表扫描 8,843 次逻辑读;有索引后 3 次¹¹。近三千倍的差距,和第 09 章 XML 索引的「XML Reader 92%」是同一个故事的两代人版本。

边界: 每加一个要过滤的 key 就要加一列——列多了表结构被 JSON 的 key「反向殖民」;这是 2025 原生索引出现前的公认痛点。

2.4 原生 JSON 索引(2025):CREATE JSON INDEX¶

解决什么问题: 计算列方案的殖民问题——一列一个索引、路径变了要重建,全交给引擎。

怎么做: CREATE JSON INDEX 名 ON 表(JSON列) [FOR ('路径1','路径2',…)] [WITH (选项)]。两个关键设计¹²:

FOR 子句点名路径(可多个);不写 FOR,引擎索引文档里出现的所有路径——灵活,但体积爆炸(下面有数字)。
存储形态不是普通 B-tree:索引实体是一张 INTERNAL_TABLE(命名规则 json_index_<表object_id>_<索引id>),能在 sys.objects / sys.indexes 里查到元数据,但内容普通用户不可读(DAC 专用管理员连接可以)¹³。

体积对比(本篇最震撼的数字): 7 万多行的表,不带 FOR 建索引——内部表从 168 KB 涨到 235,720 KB,膨胀 1,403 倍(因为它存了全部 key-path 组合,支撑任意路径搜索);重建时 FOR 点名两条路径后,体积显著缩小,而执行计划和 IO 统计几乎不变——「搜索条件明确时,点名路径的 JSON 索引能大幅减少存储消耗」¹⁴。

配套选项与限制(书里列了一整张清单)¹⁵:

限制	内容
数量	每个 JSON 列最多一个 JSON 索引;每表最多 249 个
列形态	只能建在物理 JSON 列上;计算列不行;视图/表变量/内存优化表都不行
可用性	建/改索引要离线(ONLINE=ON 不支持)
路径	FOR 里的路径不可重叠(`$.a` 和 `$.a.b` 不能同时出现)
变更	改路径 = 删了重建
其他	不支持索引提示;不支持行/页压缩

另有一个性能选项 OPTIMIZE_FOR_ARRAY_SEARCH = ON——建索引时打开,优化数组查询(书里的测试查询是 JSON_CONTAINS 在 $.Events[0].ConNote 上找单号,开索引后 5 秒内完成)¹⁶。

和计算列方案怎么选(书末对照表): JSON 索引支持 JSON_VALUE/JSON_QUERY/JSON_PATH_EXISTS/JSON_CONTAINS 全部新函数族,且不用改表结构;计算列索引胜在可定制(自己控制类型、长度、INCLUDE)且老版本可用。最优解取决于负载——表在转码文本里部分损坏,但两行结论保留:要新函数全家桶、不想改表 → JSON 索引;要精确控制或版本 < 2025 → 计算列¹⁷。

3. 作者的判断与证据¶

有实测数字的: 8,843 → 3 逻辑读(计算列)¹¹;1,403 倍体积膨胀与 FOR 缩小(原生索引)¹⁴。这两组数字构成本篇的证据骨架。

书里明说的限制清单: 原生 JSON 索引的八条限制逐条列出¹⁵——这是 2025 新功能的「诚实说明书」,作者还特意加了一句「微软团队在持续投入,这些限制多数最终会被消除」——判断句,但方向可信(微软的 JSON 投入曲线 2016→2022→2025 一路向上)。

需要打问号的: 「5 秒完成」这类绝对数字没有给前后对照的精确值(图里),只能作量级参考;1,403 倍是特定数据(文档大而路径多)下的极端值,点名 FOR 路径后的实际体积书里只说「显著缩小」没给数字——决策时先在自己的数据上量(补充判断,不在书里)。

4. 边界与局限¶

版本硬门槛:JSON_PATH_EXISTS 要 2022,JSON_CONTAINS 和原生 JSON 索引要 2025——老版本只剩计算列方案。
计算列方案的隐藏成本:JSON 里 key 改名/路径变了,计算列表达式要跟着改(又是一轮重建);列多了影响表的元数据和维护(补充判断,不在书里展开)。
原生索引不能建在计算列上——想给「CAST 出来的 JSON 列」建 JSON 索引?不行,必须是物理 JSON 类型列¹⁵。
对照表与限制清单在转码文本中部分乱码,本篇按可辨语义还原,关键数字(1,403×、249 个上限)均直接引原文¹⁴¹⁵。

5. 可带走的¶

主线一行走完: 缺 DeliveredWhen 的发票(70,500 行筛出 84)→ JSON_PATH_EXISTS 查路径存在(1/0/NULL,key 敏感,[*] 通配)→ 按值找签收人 → JSON_CONTAINS(CAST 成 JSON,通配路径,路径不存在返 NULL)→ 老库加速 → 计算列(JSON_VALUE + CAST + PERSISTED + 索引,8843 → 3 逻辑读)→ 2025 新库 → CREATE JSON INDEX(FOR 点路径,否则 1403 倍膨胀;每列一个、离线建、路径不重叠)。

**JSON_PATH_EXISTS:1/0/NULL;非法路径不报错;key 大小写敏感;数组要下标或 [*]**¹⁴⁵。
JSON_CONTAINS 只吃 JSON 类型,nvarchar 要 CAST;路径不存在返 NULL⁷⁸。
CONTAINS 查「有没有」,VALUE 取出来「比大小」——数组成员资格用前者⁹。
计算列索引三步:JSON_VALUE → CAST 收窄 → PERSISTED → 建索引¹⁰。
实测:8843 → 3 逻辑读¹¹。
原生 JSON 索引:FOR 点路径控体积(168 KB → 235,720 KB = 1403× 的反例教训)¹⁴。
JSON 索引存成 INTERNAL_TABLE,普通用户读不到内容¹³。
限制背四条:每列一个、离线建、路径不重叠、不支持压缩¹⁵。
OPTIMIZE_FOR_ARRAY_SEARCH = ON 优化数组查询¹⁶。
选型:2025 + 新函数全家桶 → 原生索引;老版本/要定制 → 计算列¹⁷。

6. 原文地图¶

主题	原书章	原文位置
JSON_PATH_EXISTS 语法与三值	Chapter 9: Converting JSON to Row Sets	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:652`(搜「checks whether」)
70,500 行筛出 84 行	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:669`(搜「84 rows」)
大小写敏感示例	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:709`(搜「ISBN」)
[*] 通配	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:737`(搜「wildcard operator」)
JSON_CONTAINS 的 CAST 要求	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:796`(搜「CAST to JSON」)
路径不存在返 NULL	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:862`(搜「does not exist」)
CONTAINS vs VALUE 分工	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:876`(搜「two very different approaches」)
计算列索引两步	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:931`(搜「two steps」)
8843 → 3 逻辑读	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:969`(搜「8843」) · `text/13-ch09-chapter-9-converting-json-to-row-sets.txt:980`(搜「logical reads 3」)
CREATE JSON INDEX 语法	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1015`(搜「CREATE JSON INDEX」)
INTERNAL_TABLE 与命名	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1114`(搜「INTERNAL_TABLE」)
1403 倍膨胀与 FOR 缩小	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1144`(搜「1,403×」) · `text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1169`(搜「significantly smaller」)
限制清单	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1193`(搜「Only one JSON index」)
两代方案对照	同上	`text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1223`(搜「computed column JSON index」)

出处:「Chapter 9: Converting JSON to Row Sets」第 651–662 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:652,搜「checks whether」)。原文:「checks whether a given SQL/JSON path exists within a JSON document. It returns 1 if the path exists, 0 if it does not, and NULL if the input is NULL」。 ↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 666–671 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:669,搜「84 rows」)。原文:「the table Invoices has over 70,500 rows, and only 84 rows* were returned by the provided filter: WHERE JSON_PATH_EXISTS(ReturnedDeliveryData,'$.DeliveredWhen') = 0」。 ↩
出处:「Chapter 9: Converting JSON to Row Sets」第 742–746 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:742,搜「lightweight」)。原文:「The JSON_PATH_EXISTS() function is a lightweight, error-free way to validate JSON paths」;「does not throw errors for invalid paths」见第 664–665 段。 ↩
出处:「Chapter 9: Converting JSON to Row Sets」第 709–715 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:709,搜「ISBN」)。原文:「The sql_json_path parameter is case-sensitive…the attribute name in the JSON data does not match the case specified in the path expression, the evaluation fails, and the function returns 0」。 ↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 716–741 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:719,搜「does not specify an array index」)与第 734–741 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:737,搜「wildcard operator」)。原文:「"websites" is defined as an array, so the path must include an index reference」「The JSON_PATH_EXISTS() function supports the wildcard operator, allowing * to match any array element」。 ↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 742–746 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:744,搜「existence checks」)。原文:「It complements existing JSON functions by focusing on existence checks rather than value extraction…especially useful in scenarios where JSON documents vary in structure」。 ↩
出处:「Chapter 9: Converting JSON to Row Sets」第 773–799 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:796,搜「CAST to JSON」)。原文:「Because JSON_CONTAINS requires input of the JSON data type, executing the function directly against varchar or nvarchar values results in an error… the column value must be CAST to JSON before calling JSON_CONTAINS」;参数(目标、搜索值、路径、search_mode)见第 775–786 段。 ↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 846–874 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:862,搜「does not exist」)。原文:「if JSON_CONTAINS is given a path expression that does not exist in the JSON structure, the function returns NULL」;两个 NULL 例($.Office[*][*] 用在单层数组、$.*[*][*][*] 越界)见第 866–873 段。 ↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 875–895 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:876,搜「two very different approaches」)。原文:「JSON_CONTAINS – Content-based filtering…Best for membership checks, especially inside arrays」「JSON_VALUE – Path-based filtering…Best for field-level comparisons」。 ↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 923–945 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:931,搜「two steps」)。原文两步:「Add a computed column to a table with a JSON_VALUE() function…The computed column must use the PERSISTED option」「I recommend converting the returned data length…closer to the original data length」,例:CAST(JSON_VALUE([ReturnedDeliveryData], '$.Events[0].ConNote') AS varchar(20)) PERSISTED;「Create an index on your computed column…can have an INCLUDE clause」。 ↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 963–985 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:969,搜「8843」)。原文:「Table 'Invoices'. Scan count 9, logical reads 8843」(无索引)与「Scan count 1, logical reads 3」(有索引);「performance is improved dramatically with index filtering」。 ↩↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 1011–1054 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1015,搜「CREATE JSON INDEX」)。语法与组件:索引名、表名、JSON 列名、可选 FOR(「If missing, SQL Server indexes all paths found in the JSON documents」)、可选 WITH 选项组。 ↩
出处:「Chapter 9: Converting JSON to Row Sets」第 1113–1128 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1114,搜「INTERNAL_TABLE」)。原文:「JSON indexes are not stored in the same way as traditional SQL Server indexes. Instead, they are stored as objects of type INTERNAL_TABLE」「regular users cannot view the actual contents…possible to inspect these internal tables using a Dedicated Administrator Connection (DAC)」;命名三段式(json_index / 表 object_id / 索引 id)见第 1123–1127 段。 ↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 1142–1179 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1144,搜「1,403×」)。原文:「its size increased from 168 KB to 235,720 KB—a 1,403× expansion」;FOR 点名两路径后:「The resulting index footprint is significantly smaller than the version created without specifying key paths」「When the search criteria are well-defined, using JSON indexes with a specified key path can significantly reduce storage consumption」。 ↩↩↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 1184–1213 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1193,搜「Only one JSON index」)。限制清单:「Only one JSON index per JSON column」「Maximum of 249 JSON indexes per table」「Computed JSON columns aren't eligible…only on physical JSON columns」「JSON indexes can't be created on JSON columns in views, table-valued variables, or memory-optimized tables」「Offline operations only」「JSON path overlap is disallowed…$a and $a.b overlap」「Path changes require index recreation」「Index hints aren't supported」「Data compression isn't supported」。 ↩↩↩↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 1084–1099 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1089,搜「CREATE JSON INDEX」)。示例:CREATE JSON INDEX IDX_Invoices_ReturnedDeliveryData ON dbo.Invoices ([ReturnedDeliveryData]) WITH (OPTIMIZE_FOR_ARRAY_SEARCH = ON);「the table with more than 70,000 rows completes its search in about five seconds」。 ↩↩
出处:「Chapter 9: Converting JSON to Row Sets」第 1222–1235 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:1223,搜「computed column JSON index」)。原文:「The comparison table below offers guidance, but the optimal choice ultimately depends on your specific workload, data patterns, and business requirements」(对照表的函数支持行列在转码文本中部分损坏,选型结论按上下文与两配方的能力描述归纳)。 ↩↩