如何在MySQL查询中将JSON对象转为键值对数组格式？

原表结构 #

期望输出结构 #

之前的错误输出 #

解决方法 #

使用MySQL 8.0及以上版本支持的JSON函数组合，将JSON对象的键值对拆分为独立行后重新聚合为目标格式：

方案1：使用JSON_EACH展开键值对 #

SELECT 
    id,
    JSON_OBJECT(
        'tags',
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'key', JSON_UNQUOTE(JSON_KEY(j.item)),
                'value', JSON_UNQUOTE(JSON_EXTRACT(j.item, '$.*'))
            )
        )
    ) AS data
FROM 
    your_table,
    JSON_EACH(data->'$.tags') AS j
GROUP BY 
    id;

方案2：使用JSON_TABLE解析键值对 #

SELECT 
    id,
    JSON_OBJECT(
        'tags',
        JSON_ARRAYAGG(
            JSON_OBJECT('key', tag_key, 'value', tag_value)
        )
    ) AS data
FROM 
    your_table,
    JSON_TABLE(
        data->'$.tags',
        '$.*' COLUMNS(
            tag_key VARCHAR(255) PATH '$."$key"',
            tag_value VARCHAR(255) PATH '$."$value"'
        )
    ) AS tag_items
GROUP BY 
    id;

说明 #

• 上述方案依赖MySQL 8.0及以上版本，低版本不支持JSON_EACH、JSON_TABLE等函数。
• 核心逻辑是先将tags下的JSON对象拆分为key-value的独立行，再通过JSON_ARRAYAGG聚合为数组，最后用JSON_OBJECT包装成目标结构。

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