如何在MySQL查询中将JSON列中的OBJECT类型值转换为ARRAY类型

嘿，这个需求完全能通过MySQL自带的JSON函数直接在查询里搞定，不用再折腾PHP啦！我给你详细说说怎么做：

首先，你已经能精准筛选出需要处理的行（那些recipients是OBJECT类型的记录），接下来核心就是把这个OBJECT转成ARRAY，同时保留JSON列里的其他键值对。

第一步：先验证转换结果（重要！避免误操作） #

在执行更新之前，建议先跑个查询确认转换后的结果是否符合预期：

SELECT
    data,
    JSON_SET(
        data,
        '$.recipients',
        (
            SELECT JSON_ARRAYAGG(recipient)
            FROM JSON_TABLE(
                JSON_EXTRACT(data, '$.recipients'),
                '$.*' COLUMNS (recipient JSON PATH '$')
            ) AS recipients
        )
    ) AS converted_data
FROM table1
WHERE JSON_TYPE(JSON_EXTRACT(data, '$.recipients')) = 'OBJECT';

这段SQL的逻辑是：

• JSON_TABLE把recipients对象里的每个子对象（比如Alice、Bob的信息）拆成单独的行记录；
• JSON_ARRAYAGG把这些子对象重新聚合成一个JSON数组；
• JSON_SET只替换原JSON中$.recipients路径的值，其他所有键值对都会原封不动保留。

第二步：执行更新操作 #

确认转换结果没问题后，就可以用UPDATE语句批量修改数据了：

UPDATE table1
SET data = JSON_SET(
    data,
    '$.recipients',
    (
        SELECT JSON_ARRAYAGG(recipient)
        FROM JSON_TABLE(
            JSON_EXTRACT(data, '$.recipients'),
            '$.*' COLUMNS (recipient JSON PATH '$')
        ) AS recipients
    )
)
WHERE JSON_TYPE(JSON_EXTRACT(data, '$.recipients')) = 'OBJECT';

需要注意的是，这个方案要求你的MySQL版本是8.0及以上，因为JSON_TABLE是8.0才引入的函数。如果你的版本低于8.0，那可能就得用PHP来处理了，但如果是8.0+的话，这个方法完全可行。

备注：内容来源于stack exchange，提问作者Erich