MariaDB嵌套JSON_ARRAYAGG替代方案：查询改写求助

问题背景 #

MariaDB旧版本不支持嵌套使用JSON_ARRAYAGG，且无法在JSON_ARRAYAGG内直接使用ORDER BY，需要重写原查询以生成预期的嵌套JSON结构结果。

改写思路 #

由于不能直接嵌套聚合JSON数组，采用分步聚合的方式：

1. 先聚合最内层的table3数据，得到每个table2条目对应的possibilities数组，并保证排序正确
2. 再将聚合后的table2数据与table1关联，聚合生成外层的choices数组

最终查询语句 #

SELECT 
    t1.name,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            "name", sub.table2_name,
            "possibilities", sub.possibilities
        )
    ) AS choices
FROM table1 t1
LEFT JOIN (
    -- 第一步：聚合每个table2对应的possibilities数组
    SELECT 
        t2.id AS table2_id,
        t2.name AS table2_name,
        t2.container AS table1_id,
        t2.sort AS table2_sort,
        JSON_ARRAYAGG(
            JSON_OBJECT("id", t3.refId, "extraCharge", t3.extraCharge)
        ) AS possibilities
    FROM table2 t2
    LEFT JOIN (
        -- 先按sort排序table3，确保聚合后顺序符合要求
        SELECT * FROM table3 ORDER BY choice, sort
    ) t3 ON t3.choice = t2.id
    GROUP BY t2.id, t2.name, t2.container, t2.sort
    -- 按table2的sort排序，保证外层聚合时choices的顺序正确
    ORDER BY t2.sort
) sub ON sub.table1_id = t1.id
GROUP BY t1.id, t1.name;

说明 #

• 内层子查询先对table3按choice和sort排序，再通过JSON_ARRAYAGG聚合，确保possibilities数组内的元素按预期顺序排列
• 中间子查询对table2按sort排序，再在外层聚合生成choices数组，保证choices内的条目顺序符合要求
• 采用分步聚合避免了嵌套JSON_ARRAYAGG的问题，同时兼容旧版本MariaDB的特性

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