Início / coding / Perguntas / 79101661
Accepted
Zourab
Asked: 2024-10-18 18:28:20 +0800 CST

Qual é a melhor maneira de obter dados agrupados de uma tabela em um objeto JSON como estrutura de árvore

  • 772

Preciso agrupar dados da tabela ORACLE em um objeto JSON como estrutura de árvore

Versão 19c do Oracle, se isso importa

A tabela ficará assim:

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;

E a estrutura do resultado deve ser assim:

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

Qual seria a melhor maneira de fazer isso?

sql

1 respostas

  1. Best Answer

    GROUP BY store, brande agregar e então usar uma segunda passagem para GROUP BY storeagregar novamente e então usar uma terceira passagem para agregar todo o conjunto de resultados:

    SELECT JSON_OBJECT(
         KEY 'items' VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'key' VALUE store,
             KEY 'summary' VALUE JSON_ARRAY(SUM(brand_quantity), SUM(brand_amount)),
             KEY 'items' VALUE JSON_ARRAYAGG(json FORMAT JSON)
           )
           RETURNING CLOB
         ),
         KEY 'summary' VALUE JSON_ARRAY(
           SUM(SUM(brand_quantity)),
           SUM(SUM(brand_amount))
         )
         RETURNING CLOB
       ) AS json
FROM   (
  SELECT store,
         SUM(quantity) AS brand_quantity,
         SUM(amount) AS brand_amount,
         JSON_OBJECT(
           KEY 'key' VALUE brand,
           KEY 'summary' VALUE JSON_ARRAY(SUM(quantity), SUM(amount)),
           KEY 'items' VALUE JSON_ARRAYAGG(
             JSON_OBJECT(
               KEY 'key' VALUE product,
               KEY 'summary' VALUE JSON_ARRAY(quantity, amount)
               RETURNING CLOB
             )
             RETURNING CLOB
           )
           RETURNING CLOB
         ) AS json
  FROM   a_table
  GROUP BY store, brand
)
GROUP BY store

    O que, para os dados da amostra, produz:

    JSON
    {"items":[{"key":"Todos os motores Loja","summary":[267,182000],"items":[{"key":"BWM","summary":[88,94000],"items":[{"key":"Carro","summary":[22,57000]},{"key":"Motocicleta","summary":[66,37000]}]},{"key":"CGM","summary":[179,88000],"items":[{"key":"Carro","summary":[88,61000]},{"key":"Bicicleta","summary":[14,2000]},{"key":"Motocicleta","summary":[77,25000]}]}]},{"key":"Veículo Loja","resumo":[29.135.300],"itens":[{"chave":"BWM","resumo":[14.64.300],"itens":[{"chave":"Carro","resumo":[2.40.000]},{"chave":"Bicicleta","resumo":[6.2.300]},{"chave":"Motocicleta","resumo":[6.22.000]}]},{"chave":"CGM","resumo":[15.71.000],"itens":[{"chave":"Carro","resumo":[8.50.000]},{"chave":"Motocicleta","resumo":[7.21.000]}]}]}],"resumo":[296.317.300]}

    violino

    • 1

relate perguntas