MySQL 5.7如何通过子查询更新JSON列并使用JSON_MERGE_PATCH合并数据

核心逻辑：先将table2的多行键值聚合为单个JSON对象，再传入JSON_MERGE_PATCH和table1的data字段合并。

适配MySQL 5.7的实现语句 #

UPDATE `table1` AS `t1`
SET `t1`.`data` = JSON_MERGE_PATCH(
  `t1`.`data`,
  (
    SELECT CAST(
      CONCAT('{', GROUP_CONCAT('"', `key`, '":"', `value`, '"' ORDER BY `id`), '}')
    AS JSON)
    FROM `table2`
  )
);

实现说明 #

• 通过GROUP_CONCAT将table2中每行的key、value拼接为"key":"value"格式的字符串，再用CONCAT包裹成完整的JSON结构
• 用CAST函数将拼接完成的字符串转为JSON类型，即可作为JSON_MERGE_PATCH的合并参数

MySQL 8.0+ 简化写法 #

如果后续升级到MySQL 8.0及以上版本，可以直接用JSON聚合函数实现，逻辑更简洁：

UPDATE `table1` AS `t1`
SET `t1`.`data` = JSON_MERGE_PATCH(
  `t1`.`data`,
  (SELECT JSON_OBJECTAGG(`key`, `value`) FROM `table2`)
);

注意事项 #

• 如果table2的key或value字段中包含双引号、特殊转义字符，需要先做转义处理，避免构造的JSON格式非法
• 若需要过滤table2中部分键值对，可以在子查询的WHERE条件中添加过滤规则

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