Início / dba / Perguntas / 322634
Accepted
Don Draper
Asked: 2023-01-27 10:16:14 +0800 CST

Como filtrar linhas com base em um campo de matriz JSON aninhado?

  • 772
[
  {
    "id": 1,
    "columns": [
      {
        "id": 16,
        "expression": "foo"
      },
      {
        "id": 12,
        "expression": "bar"
      }
    ]
  },
  {
    "id": 2,
    "columns": [
      {
        "id": 11,
        "expression": "baz"
      },
      {
        "id": 8,
        "expression": "foobar"
      }
    ]
  }
]

Eu tenho uma tabela onde uma das colunas é uma matriz JSON de elementos. Acima, você pode ver como pode ser o valor desse campo.

A ideia é filtrar as linhas das tabelas com base no valor do expressioncampo. Por exemplo, posso querer deixar apenas as linhas onde há uma coluna onde expressionequals foo.

Minha primeira ideia foi usar json_array_elements()várias vezes e, em seguida, escrever uma WHERE EXISTS(SELECT true FROM ...)cláusula, mas isso resulta em duplicatas de linha. Além disso, minhas tentativas de combinar vários json_array_elementsnem sempre funcionam da maneira que eu esperava.

Funcionaria se eu pudesse, de alguma forma, nivelar todos os itens para que houvesse uma grande lista de expressionvalores para cada linha. Infelizmente, não tenho certeza se existe uma maneira conveniente de fazer isso com essa estrutura aninhada.

ATUALIZAÇÃO: corrigido o objeto JSON

Minha tabela está assim:

id | description |      | tables
1  | sample_description | <sample_above>

O resultado desejado é manter as linhas onde existe um item no columnscampo entre os itens deste tablesarray json de forma que o expressionvalor seja igual<some_value>

postgresql

1 respostas

  1. Best Answer

    O WHERE EXISTS com json_array_elements certamente funcionaria e não geraria linhas duplicadas quando feito corretamente. Mas também não pode se beneficiar do índice gin na coluna json. Seria melhor fazê-lo da maneira que puder, como esse uso do operador de contenção.

    select * from json_table where x @> '[{"columns":[{"expression":"foo"}]}]';

    Também funcionará com ANY, embora seja bastante detalhado:

     select * from json_table where x @> ANY(ARRAY['[{"columns":[{"expression":"foo"}]}]'::jsonb,'[{"columns":[{"expression":"foot"}]}]']);

    Um exemplo do WHERE EXISTS funcionando, mas abaixo do ideal:

    select * from json_table where exists (
    select 1 from jsonb_array_elements(x) f(x2) where exists (
        select 1 from jsonb_array_elements(x2->'columns') ff(x3) where x3->>'expression' = 'foo'
    )
);
    • 1

relate perguntas