MySQL：如何批量将FileLogs数据合并到Files表的JSON字段？

1. 先聚合FileLogs的历史数据 #

要把每个文件对应的所有日志条目合并成JSON数组，必须用聚合函数处理（这是你之前只拿到第一条数据的核心原因）。以下分主流数据库给出示例：

PostgreSQL 写法 #

SELECT 
  fileId,
  jsonb_agg(
    jsonb_build_object(
      'statusId', statusId,
      'createdAt', createdAt,
      'meta', CASE WHEN meta IS NOT NULL THEN meta::jsonb ELSE '{}'::jsonb END
    ) ORDER BY createdAt DESC
  ) AS status_history
FROM FileLogs
GROUP BY fileId;

MySQL 写法 #

SELECT 
  fileId,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'statusId', statusId,
      'createdAt', createdAt,
      'meta', IF(meta IS NOT NULL, meta, JSON_OBJECT())
    ) ORDER BY createdAt DESC
  ) AS status_history
FROM FileLogs
GROUP BY fileId;

说明：

• 用jsonb_agg/JSON_ARRAYAGG将同一文件的所有日志聚合为JSON数组
• 通过CASE/IF过滤空meta，替换为空JSON对象，避免数组出现null值
• ORDER BY createdAt DESC让最新的状态排在数组最前面，方便后续查看

2. 将聚合结果合并到Files表的meta字段 #

把上面的聚合结果合并到Files的meta中，同时保留原有meta的其他数据：

PostgreSQL 更新语句 #

UPDATE Files f
SET meta = COALESCE(f.meta, '{}'::jsonb) || jsonb_build_object('statusHistory', h.status_history)
FROM (
  -- 复制上面的聚合查询
  SELECT 
    fileId,
    jsonb_agg(
      jsonb_build_object(
        'statusId', statusId,
        'createdAt', createdAt,
        'meta', CASE WHEN meta IS NOT NULL THEN meta::jsonb ELSE '{}'::jsonb END
      ) ORDER BY createdAt DESC
    ) AS status_history
  FROM FileLogs
  GROUP BY fileId
) h
WHERE f.id = h.fileId;

MySQL 更新语句 #

UPDATE Files f
JOIN (
  -- 复制上面的聚合查询
  SELECT 
    fileId,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'statusId', statusId,
        'createdAt', createdAt,
        'meta', IF(meta IS NOT NULL, meta, JSON_OBJECT())
      ) ORDER BY createdAt DESC
    ) AS status_history
  FROM FileLogs
  GROUP BY fileId
) h ON f.id = h.fileId
SET f.meta = JSON_MERGE_PRESERVE(
  IFNULL(f.meta, JSON_OBJECT()),
  JSON_OBJECT('statusHistory', h.status_history)
);

说明：

• COALESCE/IFNULL处理原有meta为空的情况，默认转为空JSON对象
• 用||（PostgreSQL）或JSON_MERGE_PRESERVE（MySQL）合并原有meta和新的历史记录，不会覆盖原有字段
• 最终Files表的meta会新增一个statusHistory字段，值为包含所有日志状态、时间和非空meta的数组

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