跳转至

修改 JSON — JSON_MODIFY 一个函数打天下,以及 XML/JSON 对比实测

这一章讲什么: XML 修改有 modify() 三条命令(insert/replace value of/delete); JSON 修改只有一个函数 JSON_MODIFY——增、改、删全靠它的第三个参数是什么。 八个配方,学的就是这一个函数的四种行为、两个配套技巧(JSON_QUERY 包层、 连续嵌套调用),外加章末一场 XML vs JSON 的计时对比。

1. 先看现象:一个函数,四种行为

本章主线例子: WideWorldImporters 的 Application.People 表,CustomFields 列存着员工的 JSON:

{
  "OtherLanguages": ["Turkish", "Slovenian"],
  "HireDate": "2010-08-24T00:00:00",
  "Title": "Team Member"
}

(取自原书配方 10-5 的示例数据。)围绕它,JSON_MODIFY 的四种行为:

  新值是普通值  → 路径存在则更新,不存在则新增(自动模式)
  新值是 NULL   → 删除该 key(key / 对象 / 数组通吃)
  路径带 append → 往数组追加(路径不存在则新建 key!)
  新值带 JSON_QUERY 包层 → 整体替换对象/数组(不转义)

2. 核心原理

2.1 自动模式:有则改,无则加

解决什么问题: 加一个 key 或改一个已有 key——最日常的两种操作,不想分开记。

怎么做: JSON_MODIFY(文档, '$.路径', 新值)。函数是 self-defined(自定义) 的:路径里的 key 已存在,就更新它的值;不存在,就把新的 key-value 追加进对象1。改 Title:JSON_MODIFY(CustomFields, '$.Title', 'Manager');加 SentBy:JSON_MODIFY(InvoiceDate, '$.SentBy', 'John Smith')——同一个写法,行为自适应2。

三个必背参数:表达式(列/变量/字面 JSON)、路径(只认 append 模式一个词)、新值3。

2.2 NULL 即删除

怎么做: 第三个参数传 NULL:JSON_MODIFY(CustomFields, '$.Title', NULL)——key-value 对消失4。对对象和数组同样适用:JSON_MODIFY(@j, '$.OtherLanguages', NULL) 删掉整个语言数组;JSON_MODIFY(@j, '$.table', NULL) 删掉整个子对象5。一条规则记三种删法。

2.3 append 模式与大小写陷阱

解决什么问题: 往已有数组里追加一个值(给语言列表加一门 Greek)。

怎么做: 路径开头加模式词:JSON_MODIFY(CustomFields, 'append $.OtherLanguages', 'Greek')6。

陷阱(书里专门给了反面教材): append 的路径不存在时,函数会新建这个 key——把 OtherLanguages 误写成小写 otherlanguages,输出里就同时存在两个数组:原样的 OtherLanguages 和新建的 otherlanguages7。再叠加一条:JSON 的 key 名大小写敏感(13 章军规),而 SQL 里写路径的失误不会有任何报错——静默复制,比报错阴得多。书里唯一的防御建议:确认路径写得和文档逐字一致8。

2.4 连续调用:函数套函数

JSON_MODIFY 返回改后的 JSON 文本,所以可以把多次修改嵌套:JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(CustomFields, 'append $.OtherLanguages', 'Greek'), '$.Title', 'Manager'), '$.CommissionRate', '1.19')——一次查询完成「追加语言 + 改职级 + 加提成率」三件事9。顺序从最内层往外读:内层先执行。

2.5 JSON_QUERY 包层:替换对象/数组的唯一正解

解决什么问题: 整个换掉一个数组(语言列表换成 Dutch/Latvian/Lithuanian)或对象。

怎么做: 新值用 JSON_QUERY('["Dutch","Latvian","Lithuanian"]') 包一层。为什么必须包:JSON_MODIFY 默认把新值当普通字符串——不包的话,数组文本里的双引号全被反斜杠转义,输出变成 "[\"Dutch\",…]" 这种字符串套字符串10。JSON_QUERY 的返回值被引擎认定为「合法 JSON」,直接嵌入不转义(11 章讲 FOR JSON 时同一个信任机制)。

重命名 key 的两步曲(本章最绕的配方): SQL Server 没有 rename。改 OtherLanguages 为 SpokenLanguages 要两步:① 用新 key 写入旧值——值怎么取?JSON_QUERY(CustomFields, '$.OtherLanguages') 取数组,但取出的文本带转义,要再套一层 JSON_QUERY 修掉(书里 Table 10-4 的「Not Corrected vs Corrected」对照演示的就是这一层之差)11;② JSON_MODIFY(…, '$.OtherLanguages', NULL) 删旧 key。两次 JSON_MODIFY 嵌套完成。

替代方案为什么不推荐: 也可以「先删整个数组、再重新插入新数组」(JSON_MODIFY(JSON_MODIFY(CustomFields, '$.OtherLanguages', NULL), '$.OtherLanguages', JSON_QUERY('…'))),结果正确,但书里明确不推荐——重新插入的数组会掉到文档最后的位置(JSON 对象的 key 有序),原位置丢失,还多一次函数调用12。作者的原话很有意思:「Inefficiency(我不用"问题"这个词,因为代码返回的结果是对的)」——正确的结果,错误的位置。

2.6 XML vs JSON:一场计时对比和选型总结

解决什么问题: 到底用 XML 还是 JSON?全书前面各章各讲各的,这一节把它们拉到同一个擂台上。

怎么做(五步实验): 建两张表(一张 XML 列、一张 nvarchar JSON 列);同样的数据分别用 FOR XML / FOR JSON 灌入(各 573 行);然后分别拆回行集,计时对比13。

数字(插入环节): JSON 20 毫秒,XML 26 毫秒——同量数据,JSON 略快;拆解环节的计时截图在转码文本中数字缺失,只保留了行数(573 rows)14。方向的结论书里给了:JSON 在插入和拆解场景「generally has a slight advantage(普遍略有优势)」15。

但书里的选型结论不是「JSON 取代 XML」,理由分三层16:

维度 XML JSON
数据形态(2022 前) 原生类型,近乎一门专用语言 纯文本(nvarchar),全靠函数操作
强项 Schema 校验、属性、命名空间、强类型 轻量、快、灵活
适用 复杂深嵌套、合同级校验 小而浅的载荷、Web API 工作流

原话收尾:「选择不该是意识形态问题……务实做法:要轻量、快、灵活用 JSON;要结构、校验、高级 Schema 支持用 XML」16。2025 年的原生 JSON 类型正在缩小差距,但「尤其在 JSON 为中心的工作负载里」——措辞留了余地。

3. 作者的判断与证据

有实测数字的: 573 行同量插入,JSON 20 毫秒 vs XML 26 毫秒14。注意量级:这是单次小批量的毫秒级差异,推不出「大数据量下 JSON 全面更快」——书里自己也只说 slight advantage(略有优势)。

明确的立场句: 「我不会推荐删了再插的技术」12;「JSON 不该在所有场景取代 XML」16;「选择不该是意识形态的」16。这三句都是作者的第一人称判断,证据支撑分别是位置丢失的对照演示、数据形态差异的事实、和全章两种格式各自的配方积累。

诚实的留白: 拆解环节的计时数字在书里是截图(转码文本缺失),书里没给文字版——我们只能引用方向性结论,不能编数字(本书拆解如实标注)。

4. 边界与局限

  • JSON_MODIFY 不支持的位置操作:不能像 XML 的 as first/as last/before/after 那样控制 key 的插入位置——JSON 对象的 key 顺序由写入顺序决定,想插队只能删了重插(又触发位置副作用)。
  • 一次调用只能改一处(一个路径),多处修改靠嵌套——嵌套深了可读性雪崩,书里最多演示三层。
  • 无 rename 是产品缺口:改名要「建新+删旧」两步曲,且取值的 JSON_QUERY 要套两层——这个配方是全章复杂度之最,暴露了函数族的设计不对称。
  • 对比实验的规模小(573 行、毫秒级),只能定方向,不能定阈值。

5. 可带走的

主线一行走完: 员工 JSON(Title、OtherLanguages 数组)→ JSON_MODIFY 改 Title(自动模式)→ NULL 删 key / 数组 / 对象通吃 → append 加 Greek(大小写错了会静默复制新数组!)→ 三层嵌套一次改三处 → JSON_QUERY 包层整体换数组(不包则满屏转义)→ 改名 = 新 key 写入(JSON_QUERY 套两层去转义)+ 旧 key 置 NULL → 对比实验:573 行 JSON 插入 20ms vs XML 26ms,选型看「轻量 vs 校验」。

  1. JSON_MODIFY 三参数:文档、路径、新值3。
  2. 自动模式:有则改、无则加2。
  3. 新值 = NULL 即删除,key/对象/数组通吃5。
  4. append 模式:数组追加;路径不存在会新建 key;key 名大小写敏感,写错 = 静默复制78。
  5. 多处修改 = 函数嵌套,内层先执行9。
  6. 整体替换对象/数组:新值必须 JSON_QUERY 包层,否则转义10。
  7. 改名的唯一路径:新 key 写旧值(QUERY 套两层)+ 删旧 key11。
  8. 「删了再插」不推荐:数组会掉到最后的位置12。
  9. 同量数据 JSON 比 XML 略快(20ms vs 26ms,573 行)——但只是 slight advantage1415。
  10. 选型务实主义:轻量灵活选 JSON,结构校验选 XML;不是意识形态之争16。

6. 原文地图

主题 原书章 原文位置
JSON_MODIFY 三参数与自动模式 Chapter 10: Modifying JSON text/14-ch10-chapter-10-modifying-json.txt:35(搜「There are three」) · text/14-ch10-chapter-10-modifying-json.txt:47(搜「self-defined」)
NULL 删除三种对象 同上 text/14-ch10-chapter-10-modifying-json.txt:97(搜「key-value pair is deleted」)
append 模式与新建陷阱 同上 text/14-ch10-chapter-10-modifying-json.txt:173(搜「optional append mode」) · text/14-ch10-chapter-10-modifying-json.txt:176(搜「creates a new key-value pair」)
嵌套连续调用 同上 text/14-ch10-chapter-10-modifying-json.txt:235(搜「continuous inline calls」)
重命名两步与双层 JSON_QUERY 同上 text/14-ch10-chapter-10-modifying-json.txt:260(搜「does not have a function to rename」) · text/14-ch10-chapter-10-modifying-json.txt:269(搜「escape character」)
替换数组与转义机理 同上 text/14-ch10-chapter-10-modifying-json.txt:319(搜「detects and validates」) · text/14-ch10-chapter-10-modifying-json.txt:324(搜「treated as a string」)
删插替代方案及其低效 同上 text/14-ch10-chapter-10-modifying-json.txt:377(搜「Inefficiency」) · text/14-ch10-chapter-10-modifying-json.txt:383(搜「would not recommend」)
对比实验步骤与 573 行 同上 text/14-ch10-chapter-10-modifying-json.txt:415(搜「comparison steps」) · text/14-ch10-chapter-10-modifying-json.txt:501(搜「573 rows」)
20ms vs 26ms 同上 text/14-ch10-chapter-10-modifying-json.txt:509(搜「20 milliseconds」)
选型总结 同上 text/14-ch10-chapter-10-modifying-json.txt:525(搜「Summary of XML and JSON」)

  1. 出处:「Chapter 10: Modifying JSON」第 72–76 段(text/14-ch10-chapter-10-modifying-json.txt:73,搜「self-defined」)。原文:「The JSON_MODIFY() function is self-defined」「when the key path is detected with a JSON string, the JSON_MODIFY() function updates the value property」。 ↩

  2. 出处:「Chapter 10: Modifying JSON」第 34–53 段(text/14-ch10-chapter-10-modifying-json.txt:48,搜「if a path contains an existing key」)。原文:「if a path contains an existing key, then the value is updated. When a key does not exist, a new key-value pair is added to the JSON string」。 ↩↩

  3. 出处:「Chapter 10: Modifying JSON」第 34–44 段(text/14-ch10-chapter-10-modifying-json.txt:35,搜「There are three」)。三个参数:Expression、Path、NewValue;「Path – Key path to a value. Supports only the append mode」。 ↩↩

  4. 出处:「Chapter 10: Modifying JSON」第 77–99 段(text/14-ch10-chapter-10-modifying-json.txt:82,搜「third」)。原文:「Set the NewValue(third) argument to NULL within the JSON_MODIFY() function if you need to delete the key-value pair」。 ↩

  5. 出处:「Chapter 10: Modifying JSON」第 95–152 段(text/14-ch10-chapter-10-modifying-json.txt:97,搜「key-value pair is deleted」)。原文:「When the JSON_MODIFY() function has a NULL listed as the third argument (NewValue), then the key-value pair is deleted from JSON. This concept applies to the key, object, and array」;数组与对象的删除示例(Listing 10-4/10-5)见第 102–150 段。 ↩↩

  6. 出处:「Chapter 10: Modifying JSON」第 153–170 段(text/14-ch10-chapter-10-modifying-json.txt:158,搜「append mode」)。原文:「The append mode of the JSON_MODIFY() function specifies that the new value needs to be added to the existing list of arrays」。 ↩

  7. 出处:「Chapter 10: Modifying JSON」第 172–190 段(text/14-ch10-chapter-10-modifying-json.txt:176,搜「creates a new key-value pair」)。原文:「When the append mode is specified, but the key path does not exist, the JSON_MODIFY() function creates a new key-value pair」;小写 otherlanguages 的复制事故见第 177–187 段。 ↩↩

  8. 出处:「Chapter 10: Modifying JSON」第 189–190 段(text/14-ch10-chapter-10-modifying-json.txt:190,搜「correct JSON path」)。原文:「To avoid such a mistake, make sure that the JSON_MODIFY() function path argument is provided with the correct JSON path」。 ↩↩

  9. 出处:「Chapter 10: Modifying JSON」第 192–237 段(text/14-ch10-chapter-10-modifying-json.txt:235,搜「continuous inline calls」)。原文:「The JSON_MODIFY() function has the ability for continuous inline calls with multiple modifications…each returned JSON string could be continuously processed for another modification」。 ↩↩

  10. 出处:「Chapter 10: Modifying JSON」第 318–331 段(text/14-ch10-chapter-10-modifying-json.txt:324,搜「treated as a string」)。原文:「the value in the newValue argument is treated as a string. Therefore, all special characters (e.g., double quotes, forward slashes, and backslashes) escape with a backslash "\". That's why the NewValue argument is surrounded by the JSON_QUERY() function to remove escape characters」。 ↩↩

  11. 出处:「Chapter 10: Modifying JSON」第 259–288 段(text/14-ch10-chapter-10-modifying-json.txt:260,搜「does not have a function to rename」)。原文:「SQL Server does not have a function to rename a key or object directly. Therefore, the rename mechanism has two steps: Create a new object with original values; Delete the old object」;双 JSON_QUERY 的转义修正见第 267–284 段(表 10-4 对照)。 ↩↩

  12. 出处:「Chapter 10: Modifying JSON」第 358–383 段(text/14-ch10-chapter-10-modifying-json.txt:377,搜「Inefficiency」)。原文:「The OtherLanguages array moves to the last JSON position, so the array loses its original place」「You are required to implement an additional JSON_MODIFY() function. I would not recommend this technique」。 ↩↩↩

  13. 出处:「Chapter 10: Modifying JSON」第 385–504 段(text/14-ch10-chapter-10-modifying-json.txt:415,搜「comparison steps」)。五步:建两表、灌入(FOR XML / FOR JSON)、拆 XML、拆 JSON、对比;代码兼容级别 130+。 ↩

  14. 出处:「Chapter 10: Modifying JSON」第 497–522 段(text/14-ch10-chapter-10-modifying-json.txt:501,搜「573 rows」)与第 506–510 段(text/14-ch10-chapter-10-modifying-json.txt:509,搜「20 milliseconds」)。原文:「In both the Table_Info_JSON and Table_Info_XML tables, 573 rows were inserted」;「JSON Insert 20 milliseconds / XML Insert 26 milliseconds」。拆解环节的计时数字在原书中为截图,转码文本未含数值,如实标注。 ↩↩↩

  15. 出处:「Chapter 10: Modifying JSON」第 525–548 段(text/14-ch10-chapter-10-modifying-json.txt:527,搜「slight advantage」)。原文:「From a performance perspective, JSON generally has a slight advantage over XML in insert and shredding scenarios」。 ↩↩

  16. 出处:「Chapter 10: Modifying JSON」第 525–566 段(text/14-ch10-chapter-10-modifying-json.txt:531,搜「does not automatically mean」)。原文:「better performance does not automatically mean JSON should replace XML in all scenarios」「the choice between XML and JSON should not be ideological」「use JSON…where lightweight, fast, and flexible data handling is required, and rely on XML where structure, validation, and advanced schema support are essential」。 ↩↩↩↩↩