SQL JSON 聚合查询与性能优化

mysql中json字段不能直接group by，需用json_extract()或->提取值；json_contains()无法走索引，应建生成列+索引；聚合用json_arrayagg()而非group_concat()；注意5.7与8.0函数差异及group_concat_max_len限制。

JSON 字段在 GROUP BY 中直接报错：Invalid use of group function

mysql 5.7+ 支持 JSON 类型，但你不能对 JSON 列直接 GROUP BY 或 ORDER BY —— 它不是标量类型，数据库不知道怎么比。常见错误是写成 GROUP BY meta（meta 是 JSON 字段），立刻报错。

必须先用函数提取出具体值，再分组。最常用的是 JSON_EXTRACT() 或更简洁的 -> 操作符：

• GROUP BY meta->"$.user_id" 等价于 GROUP BY JSON_EXTRACT(meta, "$.user_id")，但前者可走索引（如果建了函数索引）
• 注意返回值带双引号（字符串），若字段本是数字，得套一层 CAST(... AS UNSIGNED) 否则分组可能错乱
• 如果路径不存在，JSON_EXTRACT 返回 NULL，所有 NULL 会被归为同一组，容易漏数据

用 JSON_CONTAINS 做聚合筛选时慢得离谱

JSON_CONTAINS() 看起来适合查“数组里有没有某个 ID”，比如 WHERE JSON_CONTAINS(tags, '"vue"', '$')，但它无法利用索引，全表扫描是常态。尤其在百万级表上，聚合前加这条件，SUM() 或 count() 就卡住。

真正可行的优化路径只有两条：

• 把高频查询的 JSON 数组字段拆成关联表（如 post_tags），这是最彻底的解法
• 如果必须保留 JSON，就给提取路径建生成列 + 索引：

  ALTER TABLE posts ADD COLUMN tag_list VARCHAR(255) AS (JSON_EXTRACT(meta, "$.tags")) STORED;</code><br><code>CREATE INDEX idx_tag_list ON posts(tag_list);

  然后用 WHERE tag_list LIKE '%"vue"%'（不优雅但比全扫快）

• 别信 JSON_OVERLAPS()（8.0.17+），它同样不走索引，只是语法糖

聚合结果里要返回 JSON 数组，但 GROUP_CONCAT 拼出来是字符串

想把每个分组的用户邮箱聚合成 JSON 数组？直接写 GROUP_CONCAT(email) 得到的是逗号分隔字符串 "a@b.com,b@c.com"，不是合法 JSON。MySQL 5.7+ 提供了 JSON_ARRAYAGG()，这才是正解。

关键差异点：

• JSON_ARRAYAGG(email) 自动转义、加引号、处理 NULL（默认跳过），输出 ["a@b.com", "b@c.com"]
• 它支持 ORDER BY 子句：JSON_ARRAYAGG(email ORDER BY created_at DESC)，GROUP_CONCAT 也支持但不保证 JSON 合法性
• 如果字段本身是 JSON 类型（比如 profile），直接 JSON_ARRAYAGG(profile) 会嵌套两层 JSON，得用 JSON_MERGE_PRESERVE() 或先 JSON_EXTRACT 再聚合

MySQL 5.7 和 8.0 的 JSON 聚合函数行为不一致

最常踩的坑是 JSON_OBJECTAGG()：5.7 只接受两个参数（key, value），且 key 必须是标量；8.0 允许 key 是表达式，还新增了 JSON_OBJECTAGG(key, value RETURNING JSON) 显式指定返回类型。

升级或迁移时要注意：

• 5.7 中 JSON_OBJECTAGG(user_id, JSON_EXTRACT(meta, "$.score")) 是合法的；8.0 同样支持，但若 user_id 是 NULL，5.7 会跳过整行，8.0 默认报错（可设 sql_mode 临时兼容）
• JSON_PRETTY() 在 8.0 才有，5.7 里用它会直接报错，别写在共用 SQL 里
• 聚合大 JSON 时，8.0 默认 group_concat_max_len=1024 仍生效——JSON_ARRAYAGG 也受这个限制，超长会被截断，必须提前 SET session group_concat_max_len = 1000000

JSON 聚合不是“写对就行”，字段是否为空、路径是否存在、MySQL 版本、生成列索引是否覆盖——每个环节都可能让结果错位或性能崩掉。别省那几行 COALESCE() 或 IFNULL()，它们往往就是线上查不出数据的真正原因。