Início / dba / Perguntas / 313252
Accepted
wuku
Asked: 2022-06-12 15:31:23 +0800 CST

Como somar valores aninhados de uma matriz JSON com base em condições

  • 772

Eu tenho uma tabela com 3 colunas no Postgres 14.2: name, name_adds, aditional.

E essas regras:

  1. namee name_addspode ser preenchido com o mesmo valor ou não. Exemplo: nome (john) e name_adds (doe) ou ambos o valor é (john)

  2. coluna additionaltem um dos dois formatos:

    formato 1:

    {"default":[{"value_1": 100, "value_2": 0.1},{"value_1": 200, "value_2": 0.2}], "non_default":[{"value_1": 200, "value_2": 0.1}, {"value_1": 400, "value_2": 0.1}]}

    formato 2:

    [{"value_1": 10,"value_2": 11},{"value_1": 1,"value_2": 19}

  3. Se (nome = name_adds) soma padrão -> valor_1

  4. If (name != name_adds) soma non_default -> value_1

Como posso somar o formato 1 ou 2 do ponto 2. com base nas regras do ponto 3. e 4. ?

Exemplo de tabela:

name    name_adds   additional
---------------------------------------------------------------
john    john      {"default":[{"value_1": 100, "value_2": 0.1}, 
                  {"value_1": 200, "value_2": 0.2}],"non_default": 
                  [{"value_1": 200, "value_2": 0.1}, {"value_1": 400, 
                   "value_2": 0.1}]}

john     doe     {"default":[{"value_1": 100, "value_2": 0.1}, 
                  {"value_1": 200, "value_2": 0.2}],"non_default": 
                  [{"value_1": 200, "value_2": 0.1}, {"value_1": 400, 
                   "value_2": 0.1}]}

downy    downy    [{"value_1": 10, "value_2": 11},{"value_1": 1,"value_2": 19}]

downy    dan      [{"value_1": 10, "value_2": 11},{"value_1": 1,"value_2": 19}]

Resultado esperado:

name    name_adds   sum_result
---------------------------------------------------------------
john    john        300

john     doe        600

downy    downy      11

downy    dan        11

Tentei uma consulta nesta demonstração online , mas não consegui obter o resultado esperado.

postgresql json

1 respostas

  1. Best Answer
    SELECT a.name, a.name_adds, sub.*
FROM   test_json a
CROSS  JOIN LATERAL (
   SELECT sum((obj ->> 'value_1')::int) AS sum_result  -- or numeric?
   FROM   json_array_elements(COALESCE(CASE WHEN name = name_adds
                                            THEN additional::json -> 'default'
                                            ELSE additional::json -> 'non_default' END
                                     , additional::json)) obj
   ) sub;

    db<>fique aqui

    Isso depende de additional::json -> 'default'(ou 'non_default', respectivamente) ser NULL no caso de "formato 2", caso em que tomamos additional::jsondiretamente para "formato 1" usando COALESCE.
    Em seguida, desaninhar com json_array_elements()e extrair a soma value_1.

    A coluna additionaldeve ser do tipo json(ou jsonb) para começar.

    Provavelmente, você estaria muito melhor com um design relacional normalizado substituindo completamente essa coluna JSON altamente regular.

    • 2

relate perguntas