在MySQL中创建多对象JSON数组的技术实现求助

MySQL可以通过JSON_ARRAYAGG()和JSON_OBJECT()的组合实现需求，这两个函数专门用于处理JSON格式的聚合操作。

假设你的主表（例如main_table）包含id、main_name字段，关联的从表（例如detail_table）包含main_id（关联主表ID）、detail_key、detail_value字段，直接参考以下SQL示例：

SELECT
    mt.id,
    mt.main_name,
    JSON_OBJECT(
        '关联详情', JSON_ARRAYAGG(
            JSON_OBJECT(
                '属性键', dt.detail_key,
                '属性值', dt.detail_value
            )
        )
    ) AS 聚合结果
FROM main_table mt
JOIN detail_table dt ON mt.id = dt.main_id
GROUP BY mt.id, mt.main_name;

核心逻辑说明 #

• JSON_OBJECT()：将指定键值对打包成单个JSON对象，这里把聚合得到的数组放在关联详情键下。
• JSON_ARRAYAGG()：将同一主ID下的所有从表数据先转为JSON对象，再聚合成一个JSON数组。
• 必须按主表的唯一标识字段分组（比如id和main_name），确保每个主表条目对应一组聚合后的JSON数据。

如果需要保留主表中无对应从表数据的条目，把JOIN换成LEFT JOIN，同时用COALESCE()处理空值，保证空场景返回空数组：

SELECT
    mt.id,
    mt.main_name,
    JSON_OBJECT(
        '关联详情', COALESCE(
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    '属性键', dt.detail_key,
                    '属性值', dt.detail_value
                )
            ), JSON_ARRAY()
        )
    ) AS 聚合结果
FROM main_table mt
LEFT JOIN detail_table dt ON mt.id = dt.main_id
GROUP BY mt.id, mt.main_name;

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