如何将MySQL一对多关系数据迁移至JSON类型列？

你遇到的单条结果问题，核心原因是未按主表主键分组，JSON_ARRAYAGG（或对应数据库的聚合函数）默认会将所有匹配行聚合为单个数组，不分组就会把所有子表数据塞进同一行。以下是针对主流数据库的可行迁移方案：

前提假设 #

假设表结构如下（可根据实际字段调整）：

• DATA_CONNECTION（主表）：主键CONNECTION_ID，包含PROTOCOL_ID、CONNECTION_METHOD及其他可选字段
• DATA_CONNECTION_FRAGMENT（子表）：外键CONNECTION_ID关联主表，包含FRAGMENT_ID、FRAGMENT_KEY、FRAGMENT_VALUE等字段
• DATA_INTEGRATION（目标表）：包含PROTOCOL_ID、CONNECTION_METHOD、CONNECTION_DETAILS（JSON类型）及其他需迁移的字段

MySQL 迁移语句 #

INSERT INTO DATA_INTEGRATION (
    PROTOCOL_ID, 
    CONNECTION_METHOD, 
    CONNECTION_DETAILS,
    -- 追加主表其他需要迁移的字段，比如创建/更新时间
    CREATE_TIME,
    UPDATE_TIME
)
SELECT 
    dc.PROTOCOL_ID,
    dc.CONNECTION_METHOD,
    -- 用COALESCE确保无关联子表时返回空数组而非NULL
    COALESCE(
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'fragment_id', dcf.FRAGMENT_ID,
                'fragment_key', dcf.FRAGMENT_KEY,
                'fragment_value', dcf.FRAGMENT_VALUE
                -- 子表其他需要存入JSON的字段
            )
        ),
        JSON_ARRAY()
    ) AS CONNECTION_DETAILS,
    dc.CREATE_TIME,
    dc.UPDATE_TIME
FROM DATA_CONNECTION dc
-- LEFT JOIN确保主表无对应子表的行也能被迁移
LEFT JOIN DATA_CONNECTION_FRAGMENT dcf 
    ON dc.CONNECTION_ID = dcf.CONNECTION_ID
-- 必须按主表主键分组，同时包含所有SELECT中的非聚合字段
GROUP BY dc.CONNECTION_ID, dc.PROTOCOL_ID, dc.CONNECTION_METHOD, dc.CREATE_TIME, dc.UPDATE_TIME;

PostgreSQL 迁移语句 #

INSERT INTO DATA_INTEGRATION (
    protocol_id, 
    connection_method, 
    connection_details,
    create_time,
    update_time
)
SELECT 
    dc.protocol_id,
    dc.connection_method,
    COALESCE(
        json_agg(
            json_build_object(
                'fragment_id', dcf.fragment_id,
                'fragment_key', dcf.fragment_key,
                'fragment_value', dcf.fragment_value
            )
        ),
        '[]'::json
    ) AS connection_details,
    dc.create_time,
    dc.update_time
FROM data_connection dc
LEFT JOIN data_connection_fragment dcf 
    ON dc.connection_id = dcf.connection_id
GROUP BY dc.connection_id, dc.protocol_id, dc.connection_method, dc.create_time, dc.update_time;

关键注意事项 #

1. 分组规则：必须以主表主键CONNECTION_ID作为分组依据，同时GROUP BY子句要包含所有SELECT中的非聚合字段（避免数据库分组模式限制导致的错误）。
2. JOIN类型：使用LEFT JOIN而非INNER JOIN，确保主表中没有对应子表数据的行也能被迁移。
3. 空值处理：用COALESCE将无关联子表时的NULL转为空JSON数组，保证数据格式一致性。
4. 验证先行：迁移前可单独执行SELECT部分，检查返回行数是否与主表行数一致，以及CONNECTION_DETAILS的JSON结构是否符合预期。

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