MySQL聚合多行生成JSON列时子查询无法识别表名报错问题

你的SQL存在三类问题，其中直接触发IDE解析报错的是语法不规范，另外还有函数误用、逻辑不符合需求、性能低下的问题：

• 语法错误：子查询作为表达式/函数参数传入时，必须单独包裹一层圆括号。你当前写法中直接把SELECT JSON_ARRAYAGG(...)写在JSON_INSERT()的参数位，缺少外层括号，解析器无法识别这是一个合法的标量子查询，直接抛解析错误。
• 函数误用：JSON_INSERT()的作用是向已有JSON文档的指定路径插入键值，调用要求至少传入3个参数：目标JSON文档、路径表达式、待插入值，你仅传入1个参数，本身就不符合函数的调用规则，且你的聚合场景完全不需要用到这个函数。
• 逻辑偏差：你用JSON_ARRAYAGG(JSON_OBJECT(A,B))生成的结果是单键值对对象构成的数组，结构类似[{"key1":"val1"},{"key2":"val2"}]，和你要求的「A为字段名、B为对应值」的单个JSON对象结构不匹配。
• 性能问题：通过DISTINCT取唯一主键再关联子查询的写法会多次全表扫描旧表，数据量较大时执行效率很差。

直接按联合主键分组，用JSON_OBJECTAGG做键值对聚合即可，一次扫描全表就能完成计算，写法简洁且性能更好，完全匹配你的聚合规则：

INSERT INTO new_table (pk1, pk2, pk3, json_column)
SELECT
  pk1,
  pk2,
  pk3,
  JSON_OBJECTAGG(A, B) AS json_column
FROM old_table
GROUP BY pk1, pk2, pk3;

如果你使用的数据库版本过低不支持JSON_OBJECTAGG，再考虑用关联子查询的写法，注意补全子查询的外层括号，不要误用JSON_INSERT：

-- 低版本兼容写法，执行效率低于分组聚合
INSERT INTO new_table (pk1, pk2, pk3, json_column)
SELECT DISTINCT
  pk1,
  pk2,
  pk3,
  (
    SELECT JSON_OBJECTAGG(A, B)
    FROM old_table t2
    WHERE t2.pk1 = old.pk1
      AND t2.pk2 = old.pk2
      AND t2.pk3 = old.pk3
  ) AS json_column
FROM old_table AS old;

补充说明：如果同一主键下存在重复的A列值，JSON_OBJECTAGG会默认保留最后遍历到的B值，如果需要对重复键做特殊处理（比如拼接多值、取最大值），可以先在子查询中对旧表数据做去重、聚合预处理后再做JSON聚合。

内容的提问来源于stack exchange，提问作者MrCardigan