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

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

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

这一章讲什么: 关系数据 → JSON,SQL Server 的第一代工具是 FOR JSON 子句 (2016 年引入),设计和 FOR XML 对称:AUTO 模式自动、PATH 模式受控,外加三个选项。 原书第 8 章十一个配方,本篇拆前五个(FOR JSON 本体),2022/2025 年的四个新函数 和杂项在下一篇(12 章)。

1. 先看现象:JSON 是「减脂版 XML」,但脾气不同

书里给 JSON 的定位一句话:XML 的「fat-free(减脂)版」——同样的层级表达能力,没有成对的标签,所以更紧凑1。结构上 JSON 只有两种块:对象 {}(一组 key-value,key 必须唯一、字符串要带双引号)和数组 [](一串值,逗号分隔,最后一个不带逗号)2。布尔值写小写的 true/false,带前导零的数字必须当字符串加引号3。

和 XML 有一个对称得好记、但后果不同的差别:JSON 函数不挑大小写(XML 的方法名、元素名全小写军规,到这里解除了);但 key 的名字、CLR 函数的 ToString()/STAsText() 仍然挑4。

版本坐标(全章的暗线): 2016 年 FOR JSON/OPENJSON 等第一批函数;2022 年加 JSON_OBJECT/JSON_ARRAY 等构造函数;2025 年才出现原生 JSON 数据类型(2016–2022 年间 JSON 只存在 nvarchar 列里)5。

本章主线例子: 用 WideWorldImporters 库的 Website.Customers 表,把客户数据变成 JSON——先用 AUTO 偷懒,再用 PATH 精确控制,最后处理 NULL、外框数组、顶层 key 三个细节。

2. 顶层全景:五个配方一张图

  FOR JSON AUTO     → 一行一个对象,自动起名(表名/别名进 key)
  FOR JSON PATH     → 别名里的点号 = 对象层级;子查询 FOR JSON = 数组
  选项三连:
    INCLUDE_NULL_VALUES   → NULL 也保留 key(默认整个 key 消失)
    WITHOUT_ARRAY_WRAPPER → 去掉最外层的 [ ](默认整个结果是个数组)
    ROOT('名字')          → 包一层 {"名字": [ ... ]}(与上一选项互斥!)

  图说:AUTO/PATH 决定「长什么样」,三个选项决定「边界处的细节」。

3. 核心原理

3.1 AUTO 模式:自动,但自动得有自己的想法

解决什么问题: 快糙猛地把查询结果变成 JSON。

怎么做: 查询尾巴加 FOR JSON AUTO。每行变成一个对象,列名(或别名)成为 key,值照抄6。单表查询的结果形态很简单——一个对象数组:

[ { "CustomerName": "…", "PhoneNumber": "…" }, { … } ]

为什么有效 / 边界: AUTO 会「分析 FROM 和 SELECT 的结构」自动决定嵌套7,代价是它的决定不总是你想要的——多表连接时尤其如此。书里给了一个有教学意义的观察:表和列的别名会直接变成 JSON 的 key,所以 FROM sys.objects [Table] 出来的 key 就叫 "Table" 而不是 "sys.objects"——起不起别名、起什么别名,直接决定下游读 JSON 的人看到什么8。这条规律在 AUTO 和 PATH 下都成立,是 FOR JSON 里最常被忽视的设计杠杆。

3.2 NULL 的默认待遇:key 整个消失

解决什么问题: 列值是 NULL 时,JSON 里该留 "key": null 还是整个删掉?

怎么做: 默认删——FOR JSON 忽略 NULL 值的列,输出里连 key 都不出现。要保留,加 INCLUDE_NULL_VALUES:"AlternateContact":null 会显式出现在输出里9。

为什么有效: 默认删是让输出尽量小;INCLUDE_NULL_VALUES 是给下游程序一个「字段存在但无值」的明确信号——和第 02 章 XML 的 XSINIL 是同一个问题在两种格式里的答案。

边界: 这个选项只管 NULL;空字符串 '' 不是 NULL,会原样输出(补充说明,不在书里)。

3.3 外框数组与 ROOT:两个边界选项,一对冤家

解决什么问题一: 默认输出是 [ {…}, {…} ]——整个结果被一个数组包着。有些消费方要的就是单个对象流,不要这层括号。

怎么做: WITHOUT_ARRAY_WRAPPER 去掉最外层方括号10。

解决什么问题二: 给整个输出一个顶层 key(很多 API 约定 { "Customers": [ … ] })。

怎么做: ROOT('Customers')11。

互斥铁律: ROOT 和 WITHOUT_ARRAY_WRAPPER 不能同用,报错 Msg 13620——道理很直白:ROOT 的值必须挂在一个数组(或对象)上,你把数组拆了,顶层 key 没东西可挂12。

3.4 PATH 模式与点号层级:别名就是结构图

解决什么问题: AUTO 的自动嵌套不合意,要自己设计 JSON 的形状。

怎么做: FOR JSON PATH,然后在 SELECT 的别名里用点号画图——别名里每出现一个点,JSON 里就多一层对象:

SELECT db_name() AS [Database],
       [Schema].name AS [Tables.SchemaName],
       [Table].name  AS [Tables.TableName],
       [Column].name AS [Tables.Columns.ColumnName]
FROM sys.objects [Table] JOIN sys.schemas [Schema] … JOIN sys.columns [Column] …
FOR JSON PATH;

产出的层级:Database 在最外层,Tables 对象包住 SchemaName/TableName,Columns 再往下13。书里给的三层结构示意:Database → Tables(含 SchemaName/TableName)→ Columns(含 ColumnName)14。

和 XML 的 PATH 模式对照(值得记住的差异): XML 那边 PATH 用 / 斜杠分层、同名路径自动合并;JSON 这边用点号,而且没有任何自动合并——每行还是每行。

3.5 多行折进数组:内联子查询是唯一的门

解决什么问题(本章最要紧的机制): 上面点号层级有个天然缺陷。一个表有多列,想产出「每个表一个对象、列名折进一个数组」:

{ "Database": "…", "Tables": { "TableName": "SupplierCategories",
  "Columns": [ {"ColumnName":"SupplierCategoryID"}, {"ColumnName":"…"}, … ] } }

直接点号写法产出的却是:每个列重复一遍 Tables{} 对象——对象被逐行复制,输出臃肿15。

怎么做: 把「数组那部分」装进内联子查询,子查询尾巴加 FOR JSON(AUTO 或 PATH 效果相同):

SELECT db_name() AS [Database],
       [Table].name AS [Tables.TableName],
       (SELECT [Column].name AS ColumnName FROM sys.columns [Column] … FOR JSON AUTO) AS [Tables.Columns]
FROM …
FOR JSON PATH;

子查询的结果本身就是一段合法的 JSON 数组文本,外层把它整体当作 Columns 的值嵌入——数组就成型了16。书里点明:这是「对象导向 JSON」(每列一个对象、结构重复)和「紧凑 JSON」(数组收纳多行)两种风格的岔路口,按下游需求选17。

为什么有效: FOR JSON 的结果本质是文本;子查询 FOR JSON 产出的数组文本被外层「当作一个值」嵌入,天然合法。

边界: 子查询必须自己带 FOR JSON,否则外层只会把它的值当普通字符串转义(变成 \"…\" 一坨)——这正是下一篇 12 章「转义字符」配方的入口。

4. 作者的判断与证据

有对照演示支撑的: AUTO 有/无别名的输出对照8;对象式 vs 数组式两种 JSON 风格的并列演示17。都是「跑给你看」型证据,可复现。

作者的使用习惯: 格式化 JSON 用在线工具 JSONFormatter(jsonformatter.curiousconcept.com),并把备选工具(jsoneditoronline.org)也列了出来——SSMS 点开的 JSON 是单行未格式化的,手工排版大 JSON 不现实18。注意:在线工具意味着 JSON 要贴到第三方网站,敏感数据别这么干(补充判断,不在书里)。

本章的立场句: PATH 模式「你说了算」(you dictate how your JSON structure is going to look),AUTO 在多表时「不总是你期待的结果」7——和第 02 章「自定义结构用 PATH 不用 EXPLICIT」的裁决同构。

5. 边界与局限

  • 本篇只到 2022 年之前的技术:FOR JSON 拼接字符串的问题(小片段、NULL 处理不一致)正是 2022 年 JSON_OBJECT/JSON_ARRAY 要解决的,书里明说19。
  • FOR JSON 的输出是 nvarchar(max) 文本,2025 年前的版本里数据库不会校验它是不是合法 JSON(补充,不在书里明写,由「2025 才引入 JSON 类型」的事实推得)。
  • 别名作 key 时会保留特殊字符,key 含空格或点号要用方括号 […] 包住别名——和 T-SQL 标识符规则一致。
  • SSMS 的 JSON 显示坑:点开结果是 XML 编辑器、单行未格式化——是工具的显示问题,不是数据问题18。

6. 可带走的

本章主线例子,一行走完: Customers 表 → FOR JSON AUTO 得对象数组 → 别名进 key([Table] 改名示范)→ INCLUDE_NULL_VALUES 留 null key → WITHOUT_ARRAY_WRAPPER 去外框 / ROOT('Customers') 加顶层(两者互斥,Msg 13620)→ FOR JSON PATH + 点号别名造三层 → 内联子查询 FOR JSON 把多列折进数组(治对象逐行复制的臃肿)。

  1. JSON 两种块:对象 {}、数组 [];key 唯一、字符串带双引号、true/false 小写23。
  2. FOR JSON 的 key 来自别名——起别名就是设计 API8。
  3. NULL 默认连 key 消失;要占位加 INCLUDE_NULL_VALUES9。
  4. ROOT 与 WITHOUT_ARRAY_WRAPPER 互斥(Msg 13620)12。
  5. PATH 模式:别名里的点号 = 对象层级,无自动合并13。
  6. 多行折数组的唯一门:内联子查询 + FOR JSON;不写 FOR JSON 就会被当字符串转义16。
  7. AUTO 单表够用,多表用 PATH——和 FOR XML 的裁决同构7。
  8. SSMS 点开的 JSON 是单行,要格式化用工具(敏感数据慎用在线工具)18。
  9. JSON 函数不挑大小写,key 名和 ToString() 挑4。

7. 原文地图

主题原书章原文位置
JSON 定位与两种块Chapter 8: Constructing JSONtext/12-ch08-chapter-8-constructing-json.txt:4(搜「fat-free」) · text/12-ch08-chapter-8-constructing-json.txt:37(搜「Objects – Encapsulated」)
大小写规则同上text/12-ch08-chapter-8-constructing-json.txt:168(搜「key-sensitive」)
版本演进表同上text/12-ch08-chapter-8-constructing-json.txt:147(搜「evolved by SQL Server version」)
FOR JSON AUTO 与别名同上text/12-ch08-chapter-8-constructing-json.txt:177(搜「AUTO mode returns」) · text/12-ch08-chapter-8-constructing-json.txt:231(搜「take effect on JSON output」)
INCLUDE_NULL_VALUES同上text/12-ch08-chapter-8-constructing-json.txt:283(搜「INCLUDE_NULL_VALUES option」) · text/12-ch08-chapter-8-constructing-json.txt:308(搜「ignores elements with default values」)
WITHOUT_ARRAY_WRAPPER同上text/12-ch08-chapter-8-constructing-json.txt:332(搜「WITHOUT_ARRAY_WRAPPER option builds」)
ROOT 与互斥报错同上text/12-ch08-chapter-8-constructing-json.txt:363(搜「top-level key element」) · text/12-ch08-chapter-8-constructing-json.txt:388(搜「Msg 13620」)
PATH 点号层级同上text/12-ch08-chapter-8-constructing-json.txt:461(搜「each period in the alias」)
对象式 vs 数组式同上text/12-ch08-chapter-8-constructing-json.txt:477(搜「Tables{} is repeated」) · text/12-ch08-chapter-8-constructing-json.txt:499(搜「inline subquery」)
JSONFormatter 工具同上text/12-ch08-chapter-8-constructing-json.txt:212(搜「JSONFormatter」)

Footnotes

  1. 出处:「Chapter 8: Constructing JSON」第 1–5 段(text/12-ch08-chapter-8-constructing-json.txt:4,搜「fat-free」)。原文:「JSON can be thought of as a "fat-free" version of XML」。 ↩

  2. 出处:「Chapter 8: Constructing JSON」第 36–44 段(text/12-ch08-chapter-8-constructing-json.txt:37,搜「Objects – Encapsulated」)。原文:「Objects – Encapsulated within opening and closing brackets {}」「Arrays – Encapsulated within opening and closing square brackets []」;key 唯一、字符串与日期值要带双引号、布尔与数值不带,见第 50–62 段。 ↩ ↩2

  3. 出处:「Chapter 8: Constructing JSON」第 60–62 段(text/12-ch08-chapter-8-constructing-json.txt:60,搜「leading zeroes」)。原文:「Numbers with leading zeroes are considered strings. Therefore, they must be contained within double quotes」「Each member of an object and each array value must be followed by a comma, except the last key-value pair」。 ↩ ↩2

  4. 出处:「Chapter 8: Constructing JSON」第 168–170 段(text/12-ch08-chapter-8-constructing-json.txt:168,搜「key-sensitive」)。原文:「Unlike XML methods, JSON functions are not key-sensitive. However, key members remain key sensitive when referenced in JSON functions as well as the ToString() and STAsText() CLR functions」。 ↩ ↩2

  5. 出处:「Chapter 8: Constructing JSON」第 55–59 段(text/12-ch08-chapter-8-constructing-json.txt:58,搜「SQL Server 2025 introduced」)与第 147–166 段(text/12-ch08-chapter-8-constructing-json.txt:147,搜「evolved by SQL Server version」)。原文:「SQL Server 2025 introduced a native JSON data type」;演进表列出 2016(FOR JSON/OPENJSON 等)、2022(JSON_OBJECT/JSON_ARRAY 等)、2025(JSON 类型与 JSON_ARRAYAGG/OBJECTAGG 等)三批。 ↩

  6. 出处:「Chapter 8: Constructing JSON」第 177–205 段(text/12-ch08-chapter-8-constructing-json.txt:177,搜「AUTO mode returns」)。原文:「The FOR JSON clause in AUTO mode returns JSON-formatted rows」;单表 CustomerName 示例输出为对象数组。 ↩

  7. 出处:「Chapter 8: Constructing JSON」第 415–423 段(text/12-ch08-chapter-8-constructing-json.txt:416,搜「specification of the structure」)。原文:「AUTO mode builds up a JSON output automatically by figuring out how the FROM and SELECT clauses are structured」「the AUTO mode is not the best choice because your expectation for JSON output is not always the same as what AUTO mode returns. With the PATH mode, you dictate how your JSON structure is going to look」。 ↩ ↩2 ↩3

  8. 出处:「Chapter 8: Constructing JSON」第 230–277 段(text/12-ch08-chapter-8-constructing-json.txt:231,搜「take effect on JSON output」)。原文:「the table and column aliases take effect on JSON output」;对照:无别名 key 是 "sys.objects",有别名是 "Table",「Listing 8-7 is more descriptive than Listing 8-5」。 ↩ ↩2 ↩3

  9. 出处:「Chapter 8: Constructing JSON」第 278–312 段(text/12-ch08-chapter-8-constructing-json.txt:283,搜「INCLUDE_NULL_VALUES option」)与第 307–311 段(text/12-ch08-chapter-8-constructing-json.txt:308,搜「ignores elements with default values」)。原文:「By default, the FOR JSON clause ignores elements with default values. Therefore, key elements will be missing in the JSON output」。 ↩ ↩2

  10. 出处:「Chapter 8: Constructing JSON」第 327–341 段(text/12-ch08-chapter-8-constructing-json.txt:332,搜「WITHOUT_ARRAY_WRAPPER option builds」)。原文:「The WITHOUT_ARRAY_WRAPPER option builds JSON output without surrounding square brackets []」。 ↩

  11. 出处:「Chapter 8: Constructing JSON」第 358–377 段(text/12-ch08-chapter-8-constructing-json.txt:363,搜「top-level key element」)。原文:「The ROOT option adds a single top-level key element to JSON output」,输出形态 {"Customers":[ … ]}。 ↩

  12. 出处:「Chapter 8: Constructing JSON」第 379–390 段(text/12-ch08-chapter-8-constructing-json.txt:388,搜「Msg 13620」)。报错原文:「ROOT option and WITHOUT_ARRAY_WRAPPER option cannot be used together in FOR JSON」。 ↩ ↩2

  13. 出处:「Chapter 8: Constructing JSON」第 424–475 段(text/12-ch08-chapter-8-constructing-json.txt:461,搜「each period in the alias」)。原文:「When converting the result to JSON, each period in the alias builds up an additional JSON object level」,示例:「"Database": "WideWorldImporters"」来自别名 'Database',「Tables.Columns.ColumnName」三级来自点号别名。 ↩ ↩2

  14. 出处:「Chapter 8: Constructing JSON」第 447–475 段(text/12-ch08-chapter-8-constructing-json.txt:447,搜「JSON structure」)。Listing 8-19 的三层目标结构:Database → Tables_Collection(SchemaName/TableName)→ Columns_Collection(ColumnName)。 ↩

  15. 出处:「Chapter 8: Constructing JSON」第 476–496 段(text/12-ch08-chapter-8-constructing-json.txt:477,搜「Tables{} is repeated」)。原文:「The JSON object Tables{} is repeated for each column and makes the output bigger than we expected. Ideally…it would be more efficient to return the Columns section as an array」。 ↩

  16. 出处:「Chapter 8: Constructing JSON」第 498–520 段(text/12-ch08-chapter-8-constructing-json.txt:499,搜「inline subquery」)。原文:「we need to encapsulate the column name list into an inline subquery with the FOR JSON clause…for an inline subquery, both AUTO and PATH modes return the same result」。 ↩ ↩2

  17. 出处:「Chapter 8: Constructing JSON」第 516–520 段(text/12-ch08-chapter-8-constructing-json.txt:516,搜「much smaller than the JSON」)。原文:「the JSON output in Listing 8-22 is much smaller than the JSON in Listing 8-17…In some cases, you need object-oriented JSON…and in other cases, you need to create compact JSON with an array of values」。 ↩ ↩2

  18. 出处:「Chapter 8: Constructing JSON」第 202–227 段(text/12-ch08-chapter-8-constructing-json.txt:212,搜「JSONFormatter」)。原文:「the result will load as an unformatted single-line string」「One of my preferred JSON formatting tools is JSONFormatter」。 ↩ ↩2 ↩3

  19. 出处:「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」。 ↩

XML 索引(下)— 选择性索引、提示词与索引压缩

Previous Page

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

Next Page

On this page

生成 JSON(上)— FOR JSON 子句:AUTO 偷懒,PATH 掌控1. 先看现象:JSON 是「减脂版 XML」,但脾气不同2. 顶层全景:五个配方一张图3. 核心原理3.1 AUTO 模式:自动,但自动得有自己的想法3.2 NULL 的默认待遇:key 整个消失3.3 外框数组与 ROOT:两个边界选项,一对冤家3.4 PATH 模式与点号层级:别名就是结构图3.5 多行折进数组:内联子查询是唯一的门4. 作者的判断与证据5. 边界与局限6. 可带走的7. 原文地图Footnotes