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

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

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

这一章讲什么: 上一章(11/12)把数据变成 JSON;本章把它拆回来——行和列。 原书第 9 章十个配方,本篇拆前六个(解析函数与拆表),按值过滤和索引在下一篇(14 章)。

1. 先看现象:JSON 是「装着一张表的结构化字符串」

书里给 2025 年前的 JSON 一个精准定位:它不是像 XML 那样的原生类型,而是一个结构化的字符串——遵循微软的建议,标准容器是 nvarchar(max) 列1。这个定位决定了一切:数据库不会替你校验列里的内容,校验要自己动手(ISJSON),「哪个列里藏着 JSON」也得自己嗅探。

本章主线例子: WideWorldImporters 库的 Sales.Invoices 表,ReturnedDeliveryData 列存着物流配送的 JSON:谁签收(ReceivedBy)、什么时候送到的(DeliveredWhen)、事件列表(Events 数组,每个事件有时间 EventTime、状态 Status、单号 ConNote…)。

2. 顶层全景:五个函数的分工

  ISJSON(文本)              → 是 JSON 吗?(1/0/NULL;2022 起可限定类型)
  JSON_QUERY(文档, '路径')   → 取子对象/子数组(返回 JSON 文本)
  JSON_VALUE(文档, '路径')   → 取标量值(返回 nvarchar(4000))
  OPENJSON(文档 [, '路径'])   → 拆成行集(FROM 子句里配 CROSS APPLY)
       └ WITH(列名 类型 '路径', …)  → 指定输出表的列
       └ 子对象列要标 nvarchar(max) AS JSON,才能被下一层 OPENJSON 再拆

  图说:前三个是「点查」,OPENJSON 是「拆表」;嵌套结构靠 AS JSON + 多层 APPLY。

3. 核心原理

3.1 ISJSON:验明正身,以及 2022 年的类型限定

解决什么问题: 字符串列里混着 JSON 和非 JSON,先分清谁是谁。

怎么做: ISJSON(列) 返回 1(合法 JSON)、0(不合法)、NULL(输入是 NULL)。空对象 {} 和空数组 [] 都算合法2。配套工具是动态 SQL 嗅探器(和第 04 章的 XML 嗅探器同构):从 sys 视图捞出所有 varchar/nvarchar(MAX 或长度>100)列,游标逐列跑 WHERE ISJSON(列) = 1,把藏有 JSON 的列记进结果表3。两个过滤口径书里点破了:固定长度的 char/nchar 不适合存 JSON;列长小于 100 的基本不用看4。

2022 年的增强: 第二个可选参数 json_type_constraint,把「是不是 JSON」细化为「是不是那种 JSON」——OBJECT(默认,查对象)、ARRAY(查数组)、VALUE(查标量,含 null)、SCALAR(查标量,仅数字/字符串)5。书里的示例值得一抄:ISJSON('{"name":"Anna"}', ARRAY) 返 0(它是对象不是数组);ISJSON('"SQL"', VALUE) 返 1(字符串标量)6。

3.2 JSON_QUERY 与 JSON_VALUE:一对互补的点查

解决什么问题: 不要整表,只要文档里的一块(JSON_QUERY)或一个值(JSON_VALUE)。

怎么做: 两者都收 (文档, 路径);路径是 SQL/JSON path——$ 代表文档根,. 进对象的 key,[n] 取数组第 n 个。互补规则一句话:JSON_QUERY 碰标量返 NULL,JSON_VALUE 碰对象/数组返 NULL——书里给了一张四格对照表,同一路径两个函数各试一遍,哪格是 NULL 一望便知7。

SELECT JSON_QUERY(ReturnedDeliveryData, '$.Events')       AS 事件数组,     -- [ {...}, {...} ]
       JSON_QUERY(ReturnedDeliveryData, '$.Events[0]')    AS 第一个事件,   -- { … }
       JSON_VALUE (ReturnedDeliveryData, '$.ReceivedBy')  AS 签收人        -- 'Sydney'…
FROM Sales.Invoices;

两个必须记住的差异(都是和 XML 对着干的):

  1. 数组从 0 开始。书里用大写警示:「Unlike an XML singleton, which is a 1-based starting index, JSON are 0-based…must be 0, not 1」——从 XQuery 转过来的手,第一个下标写 1 就是习惯性错误8。
  2. JSON_VALUE 的返回类型是 nvarchar(4000),超长返 NULL。书里的实测:一个 5,009 字符的长文本,JSON_VALUE 拿出来是 NULL——而且不会报错(lax 模式)9。

边界: key 名含空格等特殊字符时,路径里要把 key 用双引号包起来:'$."Last Name"'10。

3.3 strict 模式:让「查不到」报错而不是给 NULL

解决什么问题: lax(默认)模式下,路径写错(比如 key 大小写不对)、值超长,都静默返 NULL——排错排到怀疑人生。

怎么做: 在路径字符串开头加 strict:JSON_VALUE(列, 'strict $.receivedby')。找不到立即报错——大小写错报 Msg 13608(Property cannot be found);超长报 Msg 13625(would be truncated)11。支持 strict 的函数有四个:JSON_VALUE、JSON_QUERY、JSON_MODIFY(下一章主角)、OPENJSON12。

又一个全小写军规: lax 和 strict 关键字区分大小写,必须小写13——本书第三条同款军规(XML 方法名、XQuery 运算符,现在是路径模式关键字)。

边界: strict 解决「查不到」的诊断,但超长问题的正解不是 strict 而是 OPENJSON(它用 nvarchar(max) 接值,没有 4000 上限)——书里明确指了这条路9。

3.4 OPENJSON:拆成行集,JSON 版的 nodes()+value() 合体

解决什么问题: 整段 JSON 变表。

怎么做,三级形态:

-- 形态一:全默认,返回三列(侦察用)
SELECT [key], [value], [type] FROM OPENJSON(@json);
-- key: 键名;value: 值文本;type: 0=null 1=string 2=int 3=bool 4=array 5=object

-- 形态二:WITH 列清单(主力形态)
SELECT UserPref.theme, UserPref.pagingType, …
FROM Application.People
CROSS APPLY OPENJSON(UserPreferences)
WITH (
    theme       varchar(20) '$.theme',
    pagingType  varchar(20) '$.table.pagingType',   -- 子对象的 key 要写实路径
    pageLength  int         '$.table.pageLength',
    favoritesOnDashboard bit '$.favoritesOnDashboard'
) AS UserPref;

WITH 里的第一级 key 有个省写技巧:不标路径时,列名必须和 JSON 里的 key 完全一致(大小写敏感),只对第一级有效;二级以下的子对象(如 table.pagingType)路径必须显式写14。

形态三:多级嵌套——AS JSON 是递归的钥匙。 拆多层结构(文档 → 对象 → 数组)时,上层 WITH 里把「还要再拆的子结构」声明成 nvarchar(max) AS JSON,它就会被当作一个独立的 JSON 值,喂给下一层 OPENJSON(配 CROSS APPLY 链):

FROM Table_Info_JSON
  CROSS APPLY OPENJSON(JSONDoc) WITH (
      [Database] varchar(30),
      [Tables]   nvarchar(MAX) AS JSON          -- ← 子对象,标记 AS JSON
  ) AS db
  CROSS APPLY OPENJSON([Tables]) WITH (
      SchemaName varchar(30), TableName varchar(30),
      [Columns]  nvarchar(MAX) AS JSON          -- ← 子数组,标记 AS JSON
  ) AS tbl
  CROSS APPLY OPENJSON([Columns]) WITH (
      ColumnName varchar(30)
  ) AS clmn;

三层各一次 APPLY,别名(db/tbl/clmn)逐级引用上一级标记为 AS JSON 的列15。要点:AS JSON 列的类型必须是 nvarchar(max)——这是上下层之间的「交接格式」16。

为什么有效: OPENJSON 是表值函数,CROSS APPLY 对每行各拆一次、行集合并——和 XML 那边 列.nodes() + CROSS APPLY 的机制完全同构17。

边界: 兼容性级别(COMPATIBILITY_LEVEL)要 130 即 SQL Server 2016 起;Application.People.UserPreferences 这种一层结构是生产常见设计,和 XML 列的「浅结构」偏好同一逻辑18。

4. 作者的判断与证据

有报错原文和实测数字的: strict 模式的 Msg 13608/1362511;5,009 字符返 NULL 的构造实验9。这两个是本篇最「硬」的证据——错误号和边界值都可复验。

书里明说的设计建议: 「微软推荐 nvarchar(max) 作为 JSON 的标准存储类型」4;「JSON_VALUE 拿到的值建议 CAST 回接近原始长度」(这是下一篇索引配方的伏笔)9。

一个值得注意的口径: 书里反复强调 JSON 的 key 是大小写敏感的(JSON_PATH_EXISTS 配方里用大写 ISBN 匹配失败返 0 演示)——和「JSON 函数不挑大小写」的表述(11 章引的)要合起来读:函数名不挑,key 名挑6。

5. 边界与局限

  • 本篇不含按值过滤和索引(9-7~9-10)——那是 14 章的内容,JSON_VALUE 的 4000 上限在索引配方里还有下文。
  • OPENJSON 的默认三列输出在转码文本里 type 值表的描述部分损坏,本篇按 0–5 的标准语义还原并标注17。
  • 嗅探器在大库上很慢(逐列 ISJSON 是全表扫描级),要挑维护窗口跑(补充判断,不在书里)。

6. 可带走的

主线一行走完: 列里混着 JSON → ISJSON 嗅探出哪些列(2022 起可限定 OBJECT/ARRAY/SCALAR)→ JSON_QUERY 取 Events 数组([0] 是第一个,从 0 开始!)→ JSON_VALUE 取 ReceivedBy 标量(4000 上限,超长返 NULL)→ 路径可疑加 strict(错则 Msg 13608,超长则 Msg 13625)→ 整拆用 OPENJSON + WITH,嵌套用 AS JSON + 多层 CROSS APPLY。

  1. ISJSON 三值:1/0/NULL;空 {} 和 [] 合法;2022 加第二参数限类型25。
  2. JSON_QUERY 取块、JSON_VALUE 取值;互相碰错类型都返 NULL7。
  3. 数组下标从 0 开始——XML 老兵的第一反应是错的8。
  4. JSON_VALUE 返回 nvarchar(4000),超长静默 NULL;要全文用 OPENJSON9。
  5. strict 让「查不到」报错;四个函数支持;关键字必须小写1113。
  6. OPENJSON 三形态:默认三列(key/value/type)、WITH 列清单、AS JSON 多层下钻15。
  7. WITH 里省路径时列名必须与 key 逐字一致(含大小写),只对第一级有效14。
  8. AS JSON 列必须 nvarchar(max)——层间交接格式16。
  9. key 名带空格:'$."Last Name"'10。
  10. 标准容器 nvarchar(max);JSON 函数名不挑大小写,key 名挑4。

7. 原文地图

主题原书章原文位置
ISJSON 三值与空块Chapter 9: Converting JSON to Row Setstext/13-ch09-chapter-9-converting-json-to-row-sets.txt:62(搜「verifies whether」) · text/13-ch09-chapter-9-converting-json-to-row-sets.txt:69(搜「Empty curly brackets」)
嗅探器的列过滤口径同上text/13-ch09-chapter-9-converting-json-to-row-sets.txt:91(搜「fixed-length」) · text/13-ch09-chapter-9-converting-json-to-row-sets.txt:106(搜「less than 100」)
2022 类型限定参数同上text/13-ch09-chapter-9-converting-json-to-row-sets.txt:141(搜「json_type_constraint」)
JSON_QUERY/VALUE 互补同上text/13-ch09-chapter-9-converting-json-to-row-sets.txt:280(搜「opposite」)
0-based 警示同上text/13-ch09-chapter-9-converting-json-to-row-sets.txt:243(搜「1-based」)
4000 上限与 5009 实验同上text/13-ch09-chapter-9-converting-json-to-row-sets.txt:302(搜「nvarchar(4000)」) · text/13-ch09-chapter-9-converting-json-to-row-sets.txt:322(搜「5,009 characters」)
strict 模式与两个错误号同上text/13-ch09-chapter-9-converting-json-to-row-sets.txt:357(搜「Msg 13608」) · text/13-ch09-chapter-9-converting-json-to-row-sets.txt:393(搜「Msg 13625」) · text/13-ch09-chapter-9-converting-json-to-row-sets.txt:375(搜「case sensitive」)
OPENJSON 的构成同上text/13-ch09-chapter-9-converting-json-to-row-sets.txt:435(搜「consists of two arguments」)
默认三列与类型表同上text/13-ch09-chapter-9-converting-json-to-row-sets.txt:489(搜「three columns」)
省路径的列名规矩同上text/13-ch09-chapter-9-converting-json-to-row-sets.txt:487(搜「case sensitive」)
AS JSON 多层下钻同上text/13-ch09-chapter-9-converting-json-to-row-sets.txt:586(搜「reflected in the FROM clause」) · text/13-ch09-chapter-9-converting-json-to-row-sets.txt:614(搜「AS JSON」)

Footnotes

  1. 出处:「Chapter 9: Converting JSON to Row Sets」第 924–930 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:924,搜「not treated as a native data type」)。原文:「Before SQL Server 2025, JSON was not treated as a native data type in SQL Server in the same way XML is. It is more comparable to a structured string…stores a table as an nvarchar(max) data type (if you follow Microsoft recommendations)」。 ↩

  2. 出处:「Chapter 9: Converting JSON to Row Sets」第 62–71 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:62,搜「verifies whether」)。原文:「returns 1 when the string is valid JSON document; 0 when invalid; NULL when an expression is a NULL value」「Empty curly brackets {} and empty square brackets [] are considered to be valid JSON」。 ↩ ↩2

  3. 出处:「Chapter 9: Converting JSON to Row Sets」第 71–137 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:71,搜「cursor is established」)。动态 SQL 嗅探器的完整流程:游标逐列生成 SELECT TOP (1) … WHERE ISJSON(列) = 1,命中记入 #Result。 ↩

  4. 出处:「Chapter 9: Converting JSON to Row Sets」第 89–107 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:91,搜「fixed-length」)。原文:「a fixed-length data type is not appropriate for JSON」;「Microsoft recommends using the nvarchar(max) data type as the standard for storing JSON」;「it is less likely to store JSON where the column length is less than 100」。 ↩ ↩2 ↩3

  5. 出处:「Chapter 9: Converting JSON to Row Sets」第 139–156 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:141,搜「json_type_constraint」)。语法 ISJSON ( expression [, json_type_constraint] );四个值:OBJECT(默认)、ARRAY、VALUE(标量含 null)、SCALAR(仅数字或字符串)。 ↩ ↩2

  6. 出处:「Chapter 9: Converting JSON to Row Sets」第 157–185 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:161,搜「Returns 1」)。示例:ISJSON('{"name":"Anna"}', ARRAY) 返 0;ISJSON('"SQL"', VALUE) 返 1;ISJSON('[1,2,3]', SCALAR) 返 0。 ↩ ↩2

  7. 出处:「Chapter 9: Converting JSON to Row Sets」第 270–300 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:280,搜「opposite」)。原文:「If you attempt to return a scalar value using the JSON_QUERY() function, NULL will be returned. The JSON_VALUE() function is the opposite – it returns NULL when the path references an object or array」。 ↩ ↩2

  8. 出处:「Chapter 9: Converting JSON to Row Sets」第 243–245 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:243,搜「1-based」)。原文:「Unlike an XML singleton, which is a 1-based starting index, JSON are 0-based…must be 0, not 1」。 ↩ ↩2

  9. 出处:「Chapter 9: Converting JSON to Row Sets」第 302–336 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:302,搜「nvarchar(4000)」)与第 320–323 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:322,搜「5,009 characters」)。原文:「The JSON_VALUE() function returns an nvarchar(4000) data type」「When the scalar value is greater than 4000 characters, the function returns a NULL」;「the OPENJSON() function…can fix this problem」。 ↩ ↩2 ↩3 ↩4 ↩5

  10. 出处:「Chapter 9: Converting JSON to Row Sets」第 337–340 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:338,搜「surrounded by double quotes」)。原文:含空格的 key 路径写作 SELECT JSON_VALUE(@json, '$."Last Name"')。 ↩ ↩2

  11. 出处:「Chapter 9: Converting JSON to Row Sets」第 341–398 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:357,搜「Msg 13608」)。报错:「Property cannot be found on the specified JSON path」;超长报错 Msg 13625:「String value in the specified JSON path would be truncated」(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:393,搜「Msg 13625」)。 ↩ ↩2 ↩3

  12. 出处:「Chapter 9: Converting JSON to Row Sets」第 360–374 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:361,搜「strict mode」)。支持 strict 的函数清单:JSON_VALUE()、JSON_QUERY()、JSON_MODIFY()、OPENJSON();「lax mode (default)…returns a scalar NULL value」「strict mode…returns an error instead of a NULL value」。 ↩

  13. 出处:「Chapter 9: Converting JSON to Row Sets」第 375–376 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:375,搜「case sensitive」)。原文:「lax and strict keywords are case sensitive; therefore, implementation is required to be in lowercase only」。 ↩ ↩2

  14. 出处:「Chapter 9: Converting JSON to Row Sets」第 476–487 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:476,搜「first-level keys」)与第 486–487 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:487,搜「case sensitive」)。原文:「The OPENJSON() function could be defined for all first-level keys by default」「all first-level keys in the WITH clause must match the JSON document name, and the name is case sensitive」。 ↩ ↩2

  15. 出处:「Chapter 9: Converting JSON to Row Sets」第 564–622 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:586,搜「reflected in the FROM clause」)。三级结构(Top「Database」→ Sub「Tables」对象 → Sub「Columns」数组)与三层 CROSS APPLY 的对应关系,完整代码见 Listing 9-23。 ↩ ↩2

  16. 出处:「Chapter 9: Converting JSON to Row Sets」第 613–616 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:614,搜「AS JSON」)。原文:「The child object name must be in the nvarchar(MAX) data type and set AS JSON object. For example, [Tables] nvarchar(MAX) AS JSON」。 ↩ ↩2

  17. 出处:「Chapter 9: Converting JSON to Row Sets」第 431–495 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:435,搜「consists of two arguments」)。原文:「The OPENJSON() function consists of two arguments and a WITH clause」;「the function can be used in the FROM clause using the CROSS APPLY and OUTER APPLY operators」;默认三列 Key/Value/Type 见第 488–495 段(表 9-1 的 type 值 0–5 对应 null/string/int/bool/array/object,转码文本中描述列局部损坏,按标准语义还原)。 ↩ ↩2

  18. 出处:「Chapter 9: Converting JSON to Row Sets」第 521–528 段(text/13-ch09-chapter-9-converting-json-to-row-sets.txt:527,搜「COMPATIBILITY_LEVEL」)。原文:「a database-independent process that can run on any database with COMPATIBILITY_LEVEL = 130 or higher」。 ↩

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

Previous Page

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

Next Page

On this page

JSON 转行集(上)— 五个解析函数与嵌套拆法1. 先看现象:JSON 是「装着一张表的结构化字符串」2. 顶层全景:五个函数的分工3. 核心原理3.1 ISJSON:验明正身,以及 2022 年的类型限定3.2 JSON_QUERY 与 JSON_VALUE:一对互补的点查3.3 strict 模式:让「查不到」报错而不是给 NULL3.4 OPENJSON:拆成行集,JSON 版的 nodes()+value() 合体4. 作者的判断与证据5. 边界与局限6. 可带走的7. 原文地图Footnotes