Oracle 19c如何将表中分组数据转为树形结构JSON对象

需求描述 #

在Oracle 19c环境中，将a_table表的数据按store、brand、product三级层级分组，转换为包含层级汇总信息的树形JSON对象。

表结构及测试数据 #

create table a_table (
    store    varchar2(100),
    brand    varchar2(100),
    product  varchar2(100),
    quantity number,
    amount   number
);

insert into a_table t (store, brand, product, quantity, amount) values ('All Motors Store', 'BWM', 'Car', 22, 57000);
insert into a_table t (store, brand, product, quantity, amount) values ('All Motors Store', 'BWM', 'Motorbike', 66, 37000);
insert into a_table t (store, brand, product, quantity, amount) values ('All Motors Store', 'CGM', 'Car', 88, 61000);
insert into a_table t (store, brand, product, quantity, amount) values ('All Motors Store', 'CGM', 'Motorbike', 77, 25000);
insert into a_table t (store, brand, product, quantity, amount) values ('All Motors Store', 'CGM', 'Bicycle', 14, 2000);
insert into a_table t (store, brand, product, quantity, amount) values ('Vehicle Store', 'BWM', 'Car', 2, 40000);
insert into a_table t (store, brand, product, quantity, amount) values ('Vehicle Store', 'BWM', 'Motorbike', 6, 22000);
insert into a_table t (store, brand, product, quantity, amount) values ('Vehicle Store', 'BWM', 'Bicycle', 6, 2300);
insert into a_table t (store, brand, product, quantity, amount) values ('Vehicle Store', 'CGM', 'Car', 8, 50000);
insert into a_table t (store, brand, product, quantity, amount) values ('Vehicle Store', 'CGM', 'Motorbike', 7, 21000);
commit;

期望生成的树形JSON结构 #

{
    "items": [
        {
            "key": "All Motors Store",
            "summary": [267, 182000],
            "items": [
                {
                    "key": "BWM",
                    "summary": [88, 94000],
                    "items": [
                        {
                            "key": "Car",
                            "summary": [22, 57000]
                        },
                        {
                            "key": "Motorbike",
                            "summary": [66, 37000]
                        }
                    ]
                },
                {
                    "key": "CGM",
                    "summary": [179, 88000],
                    "items": [
                        {
                            "key": "Bicycle",
                            "summary": [14, 2000]
                        },
                        {
                            "key": "Car",
                            "summary": [88, 61000]
                        },
                        {
                            "key": "Motorbike",
                            "summary": [77, 25000]
                        }
                    ]
                }
            ]
        },
        {
            "key": "Vehicle Store",
            "summary": [29, 135300],
            "items": [
                {
                    "key": "BWM",
                    "summary": [14, 64300],
                    "items": [
                        {
                            "key": "Bicycle",
                            "summary": [6, 2300]
                        },
                        {
                            "key": "Car",
                            "summary": [2, 40000]
                        },
                        {
                            "key": "Motorbike",
                            "summary": [6, 22000]
                        }
                    ]
                },
                {
                    "key": "CGM",
                    "summary": [15, 71000],
                    "items": [
                        {
                            "key": "Car",
                            "summary": [8, 50000]
                        },
                        {
                            "key": "Motorbike",
                            "summary": [7, 21000]
                        }
                    ]
                }
            ]
        }
    ],
    "summary": [296, 317300]
}

最优实现方案 #

利用Oracle 19c原生的JSON生成函数（JSON_OBJECT、JSON_ARRAYAGG）结合CTE（公共表表达式）分层构建树形结构，这种方式效率高、可读性强，完全依赖数据库原生能力，无需额外外部处理。

完整SQL语句 #

WITH product_level AS (
    SELECT 
        store,
        brand,
        JSON_OBJECT(
            'key' VALUE product,
            'summary' VALUE JSON_ARRAY(SUM(quantity), SUM(amount))
        ) AS product_item
    FROM a_table
    GROUP BY store, brand, product
),
brand_level AS (
    SELECT 
        store,
        JSON_OBJECT(
            'key' VALUE brand,
            'summary' VALUE JSON_ARRAY(SUM(t.quantity), SUM(t.amount)),
            'items' VALUE JSON_ARRAYAGG(p.product_item ORDER BY p.product)
        ) AS brand_item
    FROM product_level p
    JOIN a_table t USING(store, brand)
    GROUP BY store, brand
),
store_level AS (
    SELECT 
        JSON_OBJECT(
            'key' VALUE store,
            'summary' VALUE JSON_ARRAY(SUM(t.quantity), SUM(t.amount)),
            'items' VALUE JSON_ARRAYAGG(b.brand_item ORDER BY b.brand)
        ) AS store_item
    FROM brand_level b
    JOIN a_table t USING(store)
    GROUP BY store
)
SELECT JSON_OBJECT(
    'items' VALUE JSON_ARRAYAGG(s.store_item ORDER BY s.store),
    'summary' VALUE JSON_ARRAY((SELECT SUM(quantity) FROM a_table), (SELECT SUM(amount) FROM a_table))
) AS result_json
FROM store_level s;

逻辑说明 #

1. product_level：按store、brand、product三级分组，生成每个产品的JSON对象，包含产品名称（key）和该产品的数量、金额汇总（summary数组）。
2. brand_level：基于产品级结果，按store、brand分组，计算该品牌的总数量和金额，同时用JSON_ARRAYAGG聚合该品牌下的所有产品JSON对象，作为品牌节点的items。
3. store_level：再按store分组，计算该门店的总数量和金额，聚合该门店下的所有品牌JSON对象，作为门店节点的items。
4. 最终顶层：聚合所有门店的JSON对象作为顶层items，同时计算全量表的总数量和金额作为顶层summary，生成完整的树形JSON。

优势说明 #

• 性能最优：原生JSON函数由Oracle数据库优化器支持，充分利用索引和分组聚合的优化能力，比自定义PL/SQL或外部工具处理效率更高。
• 维护便捷：分层CTE结构清晰，每个层级对应树形结构的一个节点，便于后续调整字段或层级逻辑。
• 格式精准：直接生成符合需求的JSON结构，无需额外格式转换或修正。

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