编程语言研究书库
SQL Server XML 与 JSON 实例

生成 JSON(下)— 四个构造函数与转义、CLR 两个暗坑

生成 JSON(下)— 四个构造函数与转义、CLR 两个暗坑

这一章讲什么: FOR JSON(上一篇)管「整个查询结果 → JSON」。本篇的四个函数管 更碎的活儿:2022 年的 JSON_OBJECT()/JSON_ARRAY() 在单行内拼对象和数组; 2025 年的 JSON_OBJECTAGG()/JSON_ARRAYAGG() 跨行聚合,一行产出一个 对象/数组。外加两个杂项配方:转义字符的处理和 CLR 类型的转换。

1. 先看现象:为什么 FOR JSON 不够

书里把 2022 年前的处境说得很直白:拼一小段 JSON 要么手工拼字符串(易错、类型要自己转、NULL 处理不一致),要么动用整个 FOR JSON(为一个小片段杀鸡用牛刀)1。JSON_OBJECT/JSON_ARRAY 就是填这个缝的:在 SELECT 列表里就地构造合法的 JSON 片段,嵌套组合成任意结构2。

2. 顶层全景:四个函数一张分工表

              行级(单行内构造)            组级(跨行聚合,像 SUM/COUNT)
  对象        JSON_OBJECT()  (2022)        JSON_OBJECTAGG()  (2025)
  数组        JSON_ARRAY()   (2022)        JSON_ARRAYAGG()   (2025)

  图说:先问「在一行里拼,还是把多行折起来」——答案直接定位到四个格子之一。

3. 核心原理

3.1 JSON_OBJECT() 与 JSON_ARRAY():行级构造

解决什么问题: 从一行的若干列/常量,拼一个 JSON 对象或数组。

怎么做: JSON_OBJECT('key': 值, 'key2': 值2, …)——key 和值用冒号配对,逗号连接;空值语义两档:NULL ON NULL(保留 key,值是 JSON null)或 ABSENT ON NULL(整个 key 删掉,默认)3。JSON_ARRAY(值1, 值2, …) 同理,零参数返回 [];NULL ON NULL 把 SQL NULL 变成 JSON null,ABSENT ON NULL(默认)跳过4。

两个函数可以互相嵌套出层级:JSON_OBJECT('Person': JSON_ARRAY(Title, FirstName, …), 'JobTitle': JobTitle, 'Phone': JSON_ARRAY(PhoneNumberType, PhoneNumber))——Person 的值是一个数组,Phone 也是;SELECT 每行产出这样一个对象5。

为什么有效: 函数自己负责引号、转义、类型转换——手工拼字符串的三大出错源全部消失。

边界: 2022 才有的函数,旧版本没有;输出是 nvarchar,2025 起可配合 JSON 类型使用(见 3.3 的 RETURNING JSON)。

3.2 JSON_OBJECTAGG() 与 JSON_ARRAYAGG():组级聚合

解决什么问题: 把多行折进一个 JSON 结构——元数据目录式的需求:「这张表有哪些列,列名到列类型的映射」。

怎么做:

-- 对象聚合:一行的 key 列 + 一行的 value 列 → 一个对象
SELECT JSON_OBJECTAGG(sys.columns.name : sys.types.name + …)
FROM sys.columns …
GROUP BY sys.schemas.name, sys.objects.name;

-- 数组聚合:多行的值 → 一个数组(可排序)
SELECT JSON_ARRAYAGG([Name] ORDER BY [Name]) FROM Production.Location;

各自的脾气(书里两张对照表的要点)6:

函数产出保序保重复key 唯一性
JSON_ARRAYAGG["a","b",…]ORDER BY 控制保留—
JSON_OBJECTAGG{"k1":"v1",…}——强制(key 冲突会怎样书中未展开)

书里的选型口诀:简单值列表、要保序保重复 → JSON_ARRAYAGG;key-value 映射 → JSON_OBJECTAGG7。

边界: 组级聚合和 GROUP BY 配合才有意义;聚合的行数就是输出数组/对象的成员数,没有内置上限说明(补充,不在书里)。

3.3 行级 vs 组级:两对撞名函数的辨义

JSON_OBJECT 和 JSON_OBJECTAGG、JSON_ARRAY 和 JSON_ARRAYAGG 名字只差一个 AGG,书里有专门的辨析小节——判据就一条:操作级别8。

行级(无 AGG)组级(AGG)
输入一行里的表达式列表跨行的 key 列 + value 列
类比SELECT 列表SUM/COUNT
每 … 产出一个每行一个对象/数组每组一个对象/数组

「Use JSON_OBJECT when…from a single row」「Use JSON_OBJECTAGG when…aggregate multiple rows into one JSON object」——原话就是这个判据9。

3.4 转义字符:JSON_QUERY() 当「免检 JSON」

解决什么问题: 表里有一列本身存着 JSON 文本(如 InvoiceDate 列里存 {"InvoiceDate":"03/04/2013"}),直接 FOR JSON PATH 输出时,引擎不知道它是 JSON,会按普通字符串转义——内部引号前全加反斜杠,输出一坨 \"…\"10。

怎么做: 用 JSON_QUERY(列) 包一层。JSON_QUERY 的返回值是「合法 JSON」,FOR JSON 信它,原样嵌入、不加转义11。

背景知识:JSON 的非法字符表。 双引号 "、反斜杠 \、正斜杠 / 是 JSON 的麻烦字符;\r \n \t \b \f 等控制字符同样要转义12。日期字符串里的 / 正是这个配方里触发转义的元凶13。

边界: JSON_QUERY 不会帮你校验合法性——列里要是存了不合法的 JSON,它照样放行,错误在下游消费时才爆(补充判断,不在书里;ISJSON() 才是校验,见 13 章)。

3.5 CLR 类型:ToString() 的两次挑刺

解决什么问题: geography/geometry 这类 CLR 类型(SQL Server 里用 .NET 代码实现的数据类型)不能直接进 FOR JSON。

怎么做,两条路: 显式 CAST(列 AS nvarchar(1000));或调 ToString()——它把 CLR 值隐式转成 nvarchar(hierarchyid 是唯一例外,FOR JSON 直接收)14。

两个暗坑(都有错误号):

  1. 不转换直接 FOR JSON,报错 Msg 13604:「FOR JSON cannot serialize CLR objects. Cast CLR types explicitly…」15。
  2. ToString() 挑大小写——这在不挑大小写的 SQL Server 世界里是异类。写成 tostring(),报 Msg 6506(找不到方法)16。

4. 作者的判断与证据

有官方版本事实支撑的: 四个函数的引入版本(2022 一对、2025 一对)、默认 ABSENT ON NULL、RETURNING JSON 的语义——这些是产品行为,书里逐条列了语法346。

作者的评价性语句: JSON_OBJECT 是「foundational tool(基础工具)」,让 SQL Server 兼作关系库和 JSON 文档生成器17;AGG 两函数让 SQL Server「有资格和 MySQL/PostgreSQL 同台——它们早就有了类似的聚合能力」18。后一句是市场竞争视角的判断,不是技术论断。

转义与 CLR 两个配方是「踩坑记录」型内容: 错误号(13604、6506)和报错原文都给了,属于可信赖的实战证据1516。

5. 边界与局限

  • 版本门槛硬:JSON_OBJECT/ARRAY 要 2022,AGG 两兄弟和 JSON 类型要 2025——书里把演进表列在了章首,本篇各节也标了版本5。
  • RETURNING JSON 只在 2025 有意义:它把聚合结果从 nvarchar(max) 转成 JSON 数据类型;2022 的函数没有这个子句(补充判断,由语法表推断)。
  • 聚合函数的错误处理(聚合中遇非法值、超长值)书中未展开。
  • ToString() 的输出格式(WKT 文本,如地理坐标的字符串形式)表里列了转换目标类型,但具体格式不在本章范围14。

6. 可带走的

主线一行走完: 拼一小段 JSON → JSON_OBJECT/JSON_ARRAY(2022,行级,冒号配对,NULL 两档语义)→ 多行折结构 → JSON_OBJECTAGG/JSON_ARRAYAGG(2025,组级,AGG 支持 ORDER BY 与 RETURNING JSON)→ JSON 文本列进 FOR JSON → JSON_QUERY() 包一层防转义 → CLR 列 → CAST 或 ToString()(挑大小写,错则 Msg 6506)。

  1. 先问行级还是组级,再选四个函数之一8。
  2. NULL 默认删(ABSENT ON NULL);要留 null 就 NULL ON NULL3。
  3. JSON_ARRAY() 零参数返回 []4。
  4. JSON_ARRAYAGG 保序保重复;OBJECTAGG 管键值映射7。
  5. RETURNING JSON 把输出从 nvarchar 变成 JSON 类型(2025)6。
  6. JSON 文本列进 FOR JSON 必须 JSON_QUERY() 包层,否则满屏反斜杠1011。
  7. JSON 麻烦字符:" \ / 和 \r \n \t \b \f12。
  8. CLR 类型(geography/geometry)进 FOR JSON 要 CAST 或 ToString();hierarchyid 例外1415。
  9. ToString() 挑大小写,tostring() 报 Msg 650616。
  10. 版本门槛:2022 有行级函数,2025 才有组级函数和 JSON 类型5。

7. 原文地图

主题原书章原文位置
2022 前的手工拼接之痛Chapter 8: Constructing JSONtext/12-ch08-chapter-8-constructing-json.txt:546(搜「cumbersome」)
JSON_OBJECT 语法与空值语义同上text/12-ch08-chapter-8-constructing-json.txt:558(搜「JSON_OBJECT ( [ json_key_value」) · text/12-ch08-chapter-8-constructing-json.txt:565(搜「NULL ON NULL」)
JSON_ARRAY 语法与空数组同上text/12-ch08-chapter-8-constructing-json.txt:637(搜「JSON_ARRAY ( [ json_array_value」) · text/12-ch08-chapter-8-constructing-json.txt:632(搜「no arguments are provided」)
嵌套构造示例同上text/12-ch08-chapter-8-constructing-json.txt:621(搜「JSON_OBJECT('Person'」)
OBJECTAGG 的语法同上text/12-ch08-chapter-8-constructing-json.txt:697(搜「directly aggregates」) · text/12-ch08-chapter-8-constructing-json.txt:704(搜「RETURNING JSON」)
ARRAYAGG 的排序与 RETURNING同上text/12-ch08-chapter-8-constructing-json.txt:793(搜「supports ordering」) · text/12-ch08-chapter-8-constructing-json.txt:813(搜「RETURNING JSON option」)
两对函数的判据同上text/12-ch08-chapter-8-constructing-json.txt:892(搜「Scope: Works at the row level」) · text/12-ch08-chapter-8-constructing-json.txt:991(搜「row-level construction」)
转义与 JSON_QUERY 免检同上text/12-ch08-chapter-8-constructing-json.txt:1055(搜「without a JSON_QUERY」) · text/12-ch08-chapter-8-constructing-json.txt:1079(搜「trusts JSON_QUERY」)
非法字符表同上text/12-ch08-chapter-8-constructing-json.txt:1039(搜「invalid characters」)
CLR 转换与两个错误号同上text/12-ch08-chapter-8-constructing-json.txt:1104(搜「implicitly converts」) · text/12-ch08-chapter-8-constructing-json.txt:1125(搜「Msg 13604」) · text/12-ch08-chapter-8-constructing-json.txt:1128(搜「case sensitive」)
本章小结与版本叙事同上text/12-ch08-chapter-8-constructing-json.txt:1154(搜「steady evolution」)

Footnotes

  1. 出处:「Chapter 8: Constructing JSON」第 544–556 段(text/12-ch08-chapter-8-constructing-json.txt:546,搜「cumbersome」)。原文:「Working with JSON in SQL Server before 2022 was cumbersome. Developers often had to: Manually concatenate strings…Manually convert non-string data types…Handle NULL values inconsistently」。 ↩

  2. 出处:「Chapter 8: Constructing JSON」第 522–530 段(text/12-ch08-chapter-8-constructing-json.txt:527,搜「JSON_OBJECT() function」)。原文:「SQL Server 2022 introduced the JSON_OBJECT() function to easily construct JSON objects directly in T-SQL…making it simpler, cleaner, and less error-prone」。 ↩

  3. 出处:「Chapter 8: Constructing JSON」第 557–568 段(text/12-ch08-chapter-8-constructing-json.txt:558,搜「JSON_OBJECT ( [ json_key_value」)。语法与三个参数(json_key_name、value_expression、json_null_clause);「NULL ON NULL: Keeps the key with a null value」「ABSENT ON NULL: Omits the key entirely if the value is NULL (default)」。 ↩ ↩2 ↩3

  4. 出处:「Chapter 8: Constructing JSON」第 626–647 段(text/12-ch08-chapter-8-constructing-json.txt:637,搜「JSON_ARRAY ( [ json_array_value」)与第 632–634 段(text/12-ch08-chapter-8-constructing-json.txt:632,搜「no arguments are provided」)。原文:「If no arguments are provided, the function simply returns an empty JSON array ([])」。 ↩ ↩2 ↩3

  5. 出处:「Chapter 8: Constructing JSON」第 618–657 段(text/12-ch08-chapter-8-constructing-json.txt:621,搜「JSON_OBJECT('Person'」)。嵌套示例:JSON_OBJECT 内含两个 JSON_ARRAY(Person 四列、Phone 两列);版本见第 55–59 段(text/12-ch08-chapter-8-constructing-json.txt:58,搜「SQL Server 2025 introduced」)。 ↩ ↩2 ↩3

  6. 出处:「Chapter 8: Constructing JSON」第 662–701 段(text/12-ch08-chapter-8-constructing-json.txt:697,搜「directly aggregates」)与第 743–816 段(text/12-ch08-chapter-8-constructing-json.txt:766,搜「designed to construct」)。语法:「JSON_OBJECTAGG(key_expression [value_expression] [ RETURNING JSON ])」「JSON_ARRAYAGG (expression [ order by ] [ json_null ] [ RETURNING JSON ])」;「When included, the JSON array is returned as JSON data type」。 ↩ ↩2 ↩3

  7. 出处:「Chapter 8: Constructing JSON」第 818–842 段(text/12-ch08-chapter-8-constructing-json.txt:832,搜「Use JSON_ARRAYAGG when」)。原文:「Use JSON_ARRAYAGG when: You need a simple list of values. You want to preserve order and duplicates」「Use JSON_OBJECTAGG when: You need key-value mappings. You want to enforce the uniqueness of keys」。 ↩ ↩2

  8. 出处:「Chapter 8: Constructing JSON」第 883–938 段(text/12-ch08-chapter-8-constructing-json.txt:892,搜「Scope: Works at the row level」)与第 940–995 段(text/12-ch08-chapter-8-constructing-json.txt:949,搜「Operates at the row level」)。JSON_OBJECT「Creates a JSON object from explicitly defined key-value pairs in a single row」;JSON_OBJECTAGG「Works at the aggregate level (like SUM or COUNT)」。 ↩ ↩2

  9. 出处:「Chapter 8: Constructing JSON」第 931–938 段(text/12-ch08-chapter-8-constructing-json.txt:934,搜「Use JSON_OBJECT when」)。原文:「Use JSON_OBJECT when you need to build JSON from a single row. Use JSON_OBJECTAGG when you need to aggregate multiple rows into one JSON object」。 ↩

  10. 出处:「Chapter 8: Constructing JSON」第 1055–1069 段(text/12-ch08-chapter-8-constructing-json.txt:1055,搜「without a JSON_QUERY」)。原文:「If the query in Listing 8-38 executes without a JSON_QUERY() function, then JSON output will include the escape character "" in the output」。 ↩ ↩2

  11. 出处:「Chapter 8: Constructing JSON」第 1070–1080 段(text/12-ch08-chapter-8-constructing-json.txt:1079,搜「trusts JSON_QUERY」)。原文:「When a column that stores JSON is sent to JSON_QUERY(), then the FOR JSON clause trusts JSON_QUERY(), because the function always returns a valid JSON, so the output does not require escape characters」。 ↩ ↩2

  12. 出处:「Chapter 8: Constructing JSON」第 1036–1053 段(text/12-ch08-chapter-8-constructing-json.txt:1039,搜「invalid characters」)。表 8-7:双引号、反斜杠、正斜杠,及 \r、\n、\t、\b、\f。 ↩ ↩2

  13. 出处:「Chapter 8: Constructing JSON」第 1036–1038 段(text/12-ch08-chapter-8-constructing-json.txt:1036,搜「forward slashes」)。原文:「The JSON data in the column InvoiceDate has forward slashes, for example, {"InvoiceDate":"03/04/2013"}, which is an invalid character for JSON」。 ↩

  14. 出处:「Chapter 8: Constructing JSON」第 1082–1115 段(text/12-ch08-chapter-8-constructing-json.txt:1104,搜「implicitly converts」)。原文:「The ToString() function implicitly converts CLR data types geography, geometry, and hierarchyid into an nvarchar data type」;表 8-8 列出转换目标类型;hierarchyid 例外见第 1116–1119 段。 ↩ ↩2 ↩3

  15. 出处:「Chapter 8: Constructing JSON」第 1120–1127 段(text/12-ch08-chapter-8-constructing-json.txt:1125,搜「Msg 13604」)。报错原文:「FOR JSON cannot serialize CLR objects. Cast CLR types explicitly into one of the supported types in FOR JSON queries」。 ↩ ↩2 ↩3

  16. 出处:「Chapter 8: Constructing JSON」第 1128–1138 段(text/12-ch08-chapter-8-constructing-json.txt:1128,搜「case sensitive」)。原文:「The ToString() function is case sensitive, which is not typical for most SQL Server built-in functions」;报错:「Msg 6506…Could not find method 'tostring' for type 'Microsoft.SqlServer.Types.SqlGeography'」。 ↩ ↩2 ↩3

  17. 出处:「Chapter 8: Constructing JSON」第 593–602 段(text/12-ch08-chapter-8-constructing-json.txt:593,搜「not just a convenience feature」)。原文:「The JSON_OBJECT() function is not just a convenience feature; it's a foundational tool for modern database development」。 ↩

  18. 出处:「Chapter 8: Constructing JSON」第 862–881 段(text/12-ch08-chapter-8-constructing-json.txt:879,搜「competitor」)。原文:「Together, these functions streamline JSON generation and make SQL Server a strong competitor to MySQL and PostgreSQL, both of which have long offered similar JSON aggregation capabilities」。 ↩

生成 JSON(上)— FOR JSON 子句:AUTO 偷懒,PATH 掌控

Previous Page

JSON 转行集(上)— 五个解析函数与嵌套拆法

Next Page

On this page

生成 JSON(下)— 四个构造函数与转义、CLR 两个暗坑1. 先看现象:为什么 FOR JSON 不够2. 顶层全景:四个函数一张分工表3. 核心原理3.1 JSON_OBJECT() 与 JSON_ARRAY():行级构造3.2 JSON_OBJECTAGG() 与 JSON_ARRAYAGG():组级聚合3.3 行级 vs 组级:两对撞名函数的辨义3.4 转义字符:JSON_QUERY() 当「免检 JSON」3.5 CLR 类型:ToString() 的两次挑刺4. 作者的判断与证据5. 边界与局限6. 可带走的7. 原文地图Footnotes