SELECT JSON_PRETTY(
  JSON_OBJECT(
    "recipients", JSON_ARRAYAGG(
      JSON_EXTRACT(data, CONCAT('$.recipients."', k.rkey, '"'))
    )
  ) 
) AS _newjson
FROM table1
CROSS JOIN JSON_TABLE(JSON_KEYS(data, '$.recipients'), '$[*]' COLUMNS (rkey INT PATH '$')) k
WHERE JSON_TYPE(JSON_EXTRACT(data, '$.recipients')) = 'OBJECT';

字符串
输出给定的测试数据：

{
  "recipients": [
    {
      "name": "Alice",
      "email": "[email protected]"
    },
    {
      "name": "Bob",
      "email": "[email protected]"
    }
  ]
}

型

所有功劳都归功于Bill's answer做了繁重的工作。我只需要做一个调整-添加一个GROUP BY，这样聚合就不会合并组合所有行。
因为我使用JSON_SET来设置“recipients”键的值，所以子查询可以只返回转换后的值，而不需要JSON_OBSTATE（或JSON_PRETTY）。
下面是最后一个查询：

UPDATE table1
JOIN (
    SELECT
        entry_id, -- for joining back to the main table
        JSON_ARRAYAGG(JSON_EXTRACT(data, CONCAT('$.recipients."', k.recipients_key, '"'))) AS recipients
    FROM table1
    CROSS JOIN JSON_TABLE(JSON_KEYS(data, '$.recipients'), '$[*]' COLUMNS (recipients_key INT PATH '$')) k
    WHERE JSON_TYPE(JSON_EXTRACT(data, '$.recipients')) = 'OBJECT'
    GROUP BY entry_id -- important or it will combine all rows into a single result!
) AS fixed
ON fixed.entry_id = table1.entry_id
SET data = JSON_SET(data, '$.recipients', fixed.recipients)
WHERE JSON_TYPE(JSON_EXTRACT(data, '$.routed_to')) = 'OBJECT'

字符串

我发现了另一种提取值的方法，它不需要子查询，而且运行速度更快：

UPDATE table1
SET data = JSON_SET(
    data,
    '$.recipients',
    data->"$.recipients.*"
)
WHERE JSON_TYPE(JSON_EXTRACT(data, '$.recipients')) = 'OBJECT';

字符串
通过在所需JSON路径的末尾追加.*，我们可以将对象的值作为数组获取，并立即使用JSON_SET将它们写回相同的路径。