What's the best way to get grouped data from a table into a JSON object as tree structure

I need to get grouped data from ORACLE table into a JSON object as tree structure

Oracle version 19c if that matters

The table will be like this:

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;

And the result structure must be like this:

{
    "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]
}

What would be the best way to do this?

You need to sign in to view this answers