第 3.6 课 · 阅读时间：约 8 分钟

JSON 函数让查询可以查看存储在列中的 JSON 文档：按路径读取值、按值筛选行、统计数组元素，或者把行转换成供应用程序使用的 JSON。本课你将用 JSON_VALUE、JSON_EXTRACT 和 ->> 运算符读取值并据此筛选，处理数组，并用 JSON_OBJECT 和 JSON_ARRAYAGG 构建 JSON。示例在网站的多个数据库中运行，因为每种数据库对这些函数的命名都略有不同。

SQL 中的 JSON 函数

表列中的 JSON

有时一行数据的属性因行而异，表就把它们存成一个 JSON 文档。SQLite 数据库中的 machine 表就是这样存储详细信息的：

MySQL 和 MariaDB 有 JSON 类型，PostgreSQL 有 json 和 jsonb，SQLite 和 SQL Server 把 JSON 存为文本。在所有这些数据库中，JSON 函数都把文档当作结构化数据，而不是普通字符串来处理。

按路径读取值

文档中的值通过路径来指定：$ 表示整个文档，$.acquired 表示键 acquired，$[0] 表示数组的第一个元素，$[0].source 表示该元素的 source 键。

SQLite：

SELECT name, json_extract(details, '$.acquired') AS acquired
FROM machine;

结果：WY401 — 2023-05-01，Inphormex — 2021-07-15，AutoPlate 9000 — NULL。不存在的键返回 NULL，而不是报错。

SQLite 和 MySQL 还有一种简写形式，即 ->> 运算符：details ->> '$.acquired' 返回同样的值。

在 MySQL 和 MariaDB 中有两个函数，它们的区别很重要：

SELECT JSON_EXTRACT('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS extracted,
       JSON_VALUE('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS value;

结果：extracted 是带引号的 "ACADEMY DINOSAUR"，它仍然是一段 JSON；value 就是 ACADEMY DINOSAUR。

JSON_EXTRACT 返回 JSON，所以要和字符串比较时需要用 JSON_UNQUOTE 包起来。JSON_VALUE 直接返回普通值；SQL Server 也使用这个函数。

按 JSON 内部的值筛选

JSON 函数可以像其他表达式一样用在 WHERE 中。由于不存在的键返回 NULL，IS NULL 和 IS NOT NULL 可以找出不含该键和含有该键的文档。

SQLite：

SELECT name, json_extract(details, '$.refurbished') AS refurbished
FROM machine
WHERE json_extract(details, '$.refurbished') IS NOT NULL;

结果：一台机器 Inphormex，于 2023-10-22 翻新。

JSON 数组

在 University 数据库（MariaDB）中，research_projects.funding 存储一个资助数组：[{ "source": "Private Foundation", "amount": 244542.96, ...}]，没有资助的项目则为 []。JSON_LENGTH 统计元素个数，带索引的路径读取其中一个元素：

SELECT project_id, JSON_LENGTH(funding) AS grants,
       JSON_UNQUOTE(JSON_EXTRACT(funding, '$[0].source')) AS first_source
FROM research_projects
ORDER BY project_id
LIMIT 3;

结果：项目 1 有 1 笔来自 Private Foundation 的资助；项目 2 和 3 的资助数为 0，第一个来源为 NULL。

要检查数组中是否有任何元素包含某个值，MySQL 和 MariaDB 提供 JSON_SEARCH，它返回找到的值的路径，找不到时返回 NULL：

SELECT COUNT(*) AS nasa_projects
FROM research_projects
WHERE JSON_SEARCH(funding, 'one', 'NASA', NULL, '$[*].source') IS NOT NULL;

结果：30 个项目有来自 NASA 的资助。`$[].source` 表示"每个元素的 source"。*

PostgreSQL：-> 和 ->> 运算符

PostgreSQL 用运算符而不是路径来读取 JSON：-> 返回 JSON 值，->> 返回文本。在 Bookings 数据库中，机场名称以 jsonb 形式存储多种语言，例如 { "en": "Yakutsk Airport", "ru": "Якутск"}：

SELECT airport_code, airport_name ->> 'en' AS name, city ->> 'en' AS city
FROM airports_data
ORDER BY airport_code
LIMIT 3;

结果：AAQ — Anapa Vityazevo Airport，Anapa；ABA — Abakan Airport，Abakan；AER — Sochi International Airport，Sochi。

从行构建 JSON

相反的任务是把结果以 JSON 形式返回，例如给 Web 应用程序使用。JSON_OBJECT 用键值对构建一个对象。

MySQL，Sakila：

SELECT JSON_OBJECT('id', film_id, 'title', title, 'rating', rating) AS film
FROM film
ORDER BY film_id
LIMIT 2;

结果：{ "id": 1, "title": "ACADEMY DINOSAUR", "rating": "PG"} 和 { "id": 2, "title": "ACE GOLDFINGER", "rating": "G"}。

JSON_ARRAYAGG 是一个聚合函数：它把多行的值收集到一个数组中。

SELECT JSON_ARRAYAGG(name) AS categories
FROM category;

结果：一行，包含全部 16 个类别的数组：["Action", "Animation", "Children", ...]。

在 PostgreSQL 中，用 json_build_object 和 json_agg 完成同样的事情：

SELECT json_build_object('code', airport_code, 'city', city ->> 'en') AS airport
FROM airports_data
ORDER BY airport_code
LIMIT 2;

结果：{ "code" : "AAQ", "city" : "Anapa"} 和 { "code" : "ABA", "city" : "Abakan"}。

不同数据库中的 JSON 函数

什么时候存储 JSON

对于因行而异的属性，或者从外部系统以文档形式传来的数据，JSON 很方便。但 JSON 内部的值比普通列更难校验、连接和建立索引。如果某个键在每一行都存在并且用于筛选，就应该为它单独建一列。

本课要点：

• 路径用于指定 JSON 内部的值：$.key、$[0]、$[0].key、$[*].key。
• JSON_VALUE（MySQL、MariaDB、SQL Server）、json_extract 和 ->>（SQLite、MySQL）以及 ->>（PostgreSQL）以普通值的形式读取值。
• MySQL 和 MariaDB 中的 JSON_EXTRACT 返回 JSON：字符串带着引号返回，需要用 JSON_UNQUOTE。
• 不存在的键返回 NULL，所以 IS NULL 和 IS NOT NULL 可以按是否含有某个键来筛选文档。
• JSON_OBJECT 根据列构建对象，JSON_ARRAYAGG 把行收集到数组中；PostgreSQL 中对应的是 json_build_object 和 json_agg。

常见问题

JSON_EXTRACT 和 JSON_VALUE 有什么区别？

JSON_EXTRACT 返回一段 JSON：字符串带引号返回，对象或数组以 JSON 文本返回。JSON_VALUE 返回单个标量值，形式为普通文本，可以直接用于比较或显示。

键不存在时，JSON 函数返回什么？

返回 NULL。因此 WHERE json_extract(details, '$.refurbished') IS NULL 能找到不含这个键的文档。

PostgreSQL 中 -> 和 ->> 有什么区别？

-> 返回 JSON 值，->> 返回文本。用 -> 深入文档内部，用 ->> 取得最终的值。

面试题

什么时候你会把数据存进 JSON 列，而不是拆成多个列？

当属性因行而异、很少用于筛选，或者是从其他系统以现成文档形式传来时。每一行都有、并且用于筛选、连接或约束的值，应该放在普通列中。

如何找出 JSON 数组中包含某个值的行？

在 MySQL 和 MariaDB 中用 JSON_SEARCH(doc, 'one', 'value', NULL, '$[*].key') IS NOT NULL 或 JSON_CONTAINS；在 PostgreSQL 中对 jsonb 使用包含运算符 @>。

如何把查询结果以 JSON 形式返回？

用 JSON_OBJECT（PostgreSQL 中为 json_build_object）构建对象，再用 JSON_ARRAYAGG（json_agg）把它们收集到数组中；SQL Server 则在查询末尾加上 FOR JSON PATH。