如何修改MySQL存储过程以生成按类型分组聚合的预期JSON数组格式

修改后的存储过程代码 #

CREATE PROCEDURE streetuk_dev.ob_get_category_data()
BEGIN
    SELECT JSON_ARRAYAGG(category_item) AS category_data
    FROM (
        -- 按类型分组，聚合同类型下的所有子分类为数组
        SELECT JSON_OBJECT(
            'name', LOWER(type),
            'transaction_categories', JSON_ARRAYAGG(DISTINCT bud_sub_category)
        ) AS category_item
        FROM ob_category
        GROUP BY type
    ) AS temp;
END

核心修改逻辑 #

• 新增内层子查询，先按type字段分组，避免不同类型的数据混聚
• 分组内使用JSON_ARRAYAGG将同类型下的所有bud_sub_category值聚合成数组，作为transaction_categories的取值
• 如果不需要统一name字段为小写，直接将LOWER(type)替换为type即可
• 如果不需要对子分类去重，删除JSON_ARRAYAGG中的DISTINCT关键字即可
• 外层将每个分组生成的单个类型JSON对象，再次用JSON_ARRAYAGG聚合为最终的数组结构，即可得到你预期的输出格式

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