Início / dba / Perguntas / 130699
Accepted
BartZ
Asked: 2016-02-28 17:49:21 +0800 CST

Matriz de consulta PostgreSQL JSON em vários valores

  • 772

Eu quero escrever uma consulta contra o jsonbtipo no Postgres que, dada uma matriz de IDs de clientes, encontre os grupos correspondentes.

Dada esta tabela de exemplo:

CREATE TABLE grp(d JSONB NOT NULL);

INSERT INTO grp VALUES
   ('{"name":"First","arr":["foo"], "customers":[{"id":"1", "name":"one"},{"id":"2", "name":"two"}]}')
 , ('{"name":"Second","arr":["foo","bar"], "customers":[{"id":"3", "name":"three"},{"id":"4", "name":"four"}]}')
 , ('{"name":"Third","arr":["bar","baz"], "customers":[{"id":"5", "name":"five"},{"id":"6", "name":"seven"}]}');

Encontrei uma pergunta semelhante ( PostgreSql JSONB SELECT contra vários valores ) e consegui alcançar o que quero em uma matriz simples usando esta consulta:

SELECT d FROM grp WHERE d->'arr' ?| ARRAY['foo', 'bar'];

No entanto, não consigo fazer funcionar quando array contém objetos JSON :

SELECT d FROM grp WHERE d->'customers' ?| ARRAY['{"id":"1"}', '{"id":"5"}'];

Aqui está o que eu espero da minha consulta:

grp "Primeiro" -> cliente "1"

grp "Terceiro" -> cliente "5"

postgresql postgresql-9.4

1 respostas

  1. Best Answer

    Existe uma maneira: combine o operador de contenção@> com a ANYconstrução :

    SELECT d
FROM   grp
WHERE  d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);

    Ou:

    ...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);

    É essencial converter a matriz jsonb[]explicitamente. E observe que cada elemento é uma matriz JSON contendo um objeto dentro como o operador @>exige. Portanto, é uma matriz de matrizes JSON.

    Você pode usar um índice para isso:

    O manual afirma explicitamente que o operador ?|é apenas para strings correspondentes a chaves ou elementos de matriz (não valores ):

    Alguma das strings na matriz de texto existe como chaves de nível superior ou elementos da matriz?

    • 32

relate perguntas