MySQL 8.0 中用 JSON_ARRAYAGG() 将多行聚合为 JSON 数组,需配合 GROUP BY,自动忽略 NULL,支持内部 ORDER BY 排序及与 JSON_OBJECT 组合生成对象数组,返回 JSON 类型而非字符串。

MySQL 8.0 中用 JSON_ARRAYAGG() 把多行转成 JSON 数组
直接用 JSON_ARRAYAGG(),它是 MySQL 8.0+ 原生聚合函数,专为“行→数组”设计。别手写 CONCAT('[', ...) 或拼接字符串,那不是 JSON,只是长得像,且无法被 JSON 函数解析。
常见错误是误用 JSON_OBJECT() 单条记录封装后不聚合,结果只返回一行一个对象,不是数组;或者漏掉 GROUP BY 导致全表压成一个数组(有时是预期行为,但多数场景需按业务分组)。
-
JSON_ARRAYAGG()必须配合GROUP BY使用,否则默认对整个结果集聚合 - 它会自动跳过
NULL值,不需要额外WHERE col IS NOT NULL - 若字段本身是 JSON 类型(如
json列),JSON_ARRAYAGG()保留其结构;若为字符串或数字,会自动转义并加引号(字符串)或不加引号(数字)
示例:查用户订单 ID 列表
SELECT user_id, JSON_ARRAYAGG(order_id) AS order_ids FROM orders GROUP BY user_id;
处理嵌套结构:用 JSON_OBJECT + JSON_ARRAYAGG 组合
单纯数组不够,要带字段名的键值对数组(比如 [{id:1,name:"a"},{id:2,name:"b"}]),就得先用 JSON_OBJECT() 每行生成一个对象,再用 JSON_ARRAYAGG() 聚合成数组。
容易踩的坑是顺序写反:先 JSON_ARRAYAGG(JSON_OBJECT(...)) 是对的;反过来 JSON_OBJECT('items', JSON_ARRAYAGG(...)) 是生成一个含数组字段的对象,不是数组本身。
- 字段名必须是字符串字面量(如
'id'),不能是变量或列别名 - 如果某列可能为
NULL,JSON_OBJECT()仍会保留该 key,值为NULL;如需过滤掉空字段,得用IF(col IS NOT NULL, col, JSON_REMOVE(...))等方式,但通常不必要 - 性能上,嵌套越深、字段越多,JSON 构造开销越大;大数据量时建议在应用层组装,而非全靠 MySQL
示例:生成带 id 和 title 的文章对象数组
SELECT category,
JSON_ARRAYAGG(
JSON_OBJECT('id', id, 'title', title)
) AS posts
FROM articles
GROUP BY category;
ORDER BY 在 JSON_ARRAYAGG 内部生效吗?
能,但语法位置很关键:必须写在 JSON_ARRAYAGG() 函数内部,用 ORDER BY 子句,不是外面的 ORDER BY。
使用 extruct 从原始 HTML 中提取机器可读的元数据,以便代理可以在不依赖脆弱的全页解析的情况下对页面进行分类、去重或丰富。它最适合元数据采集工作流,而不适用于爬取整个网站或渲染 JavaScript 密集型页面。
外部 ORDER BY 只影响最终结果行序,不影响数组内元素顺序;很多人写了外面的排序却没效果,就是这个原因。
- 写法是:
JSON_ARRAYAGG(col ORDER BY col DESC) - 支持多字段排序:
JSON_ARRAYAGG(id ORDER BY status ASC, created_at DESC) - 排序依据的是原始列值,不是 JSON 化后的值;所以别指望对字符串字段按“语义”排序(比如数字字符串 '10' 排在 '2' 前),该转类型就转
示例:按价格升序排列商品 ID 数组
SELECT shop_id,
JSON_ARRAYAGG(product_id ORDER BY price ASC) AS cheap_first
FROM products
GROUP BY shop_id;
遇到 “This function has none of DETERMINISTIC…” 错误怎么办
执行含 JSON_ARRAYAGG() 的视图或存储函数时,可能报错:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA。这是因为 MySQL 要求函数声明明确特性,而 JSON 函数默认未标记。
根本原因不是 JSON 函数本身有问题,而是你创建的视图/函数没加 DETERMINISTIC(或对应修饰符)。
- 创建函数时,必须显式加上
DETERMINISTIC——JSON_ARRAYAGG是确定性的(输入相同,输出必相同) - 视图不会直接报这个错,但如果视图被用于函数或触发器中,上游调用链可能触发校验
- 临时绕过方法是设
SET SESSION sql_mode = '';,但不推荐,应修正定义
正确函数定义片段:
CREATE FUNCTION get_user_orders(u_id INT)
RETURNS JSON
READS SQL DATA
DETERMINISTIC
BEGIN
RETURN (
SELECT JSON_ARRAYAGG(order_id)
FROM orders WHERE user_id = u_id
);
END;
注意 JSON_ARRAYAGG 返回的是 JSON 类型值,不是字符串;如果后续要 JSON_EXTRACT 或 JSON_CONTAINS,必须确保上游没用 CAST(... AS CHAR) 或隐式转字符串,否则 JSON 函数会静默失败。

















