Início / dba / Perguntas / 89971
Accepted
jgm
Asked: 2015-01-23 14:02:54 +0800 CST

PostgreSql JSONB SELECT contra vários valores

  • 772

Eu tenho uma tabela JSON muito simples que preencho com alguns dados de amostra:

CREATE TABLE jsonthings(d JSONB NOT NULL);

INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}');
INSERT INTO jsonthings VALUES ('{"name":"Second","tags":["foo","bar"]}');
INSERT INTO jsonthings VALUES ('{"name":"Third","tags":["bar","baz"]}');
INSERT INTO jsonthings VALUES ('{"name":"Fourth","tags":["baz"]}');

CREATE INDEX ON jsonthings USING GIN(d);

E estou tentando usar o índice ao executar um arquivo SELECT. Um simples SELECTpara obter as linhas onde o valor é um único item funciona muito bem:

SELECT d FROM jsonthings WHERE d @> '{"name":"First"}';

Mas ao tentar executar uma consulta que corresponda a mais de um valor name, não consigo descobrir como usar o índice. Eu tentei:

SELECT d FROM jsonthings WHERE d->>'name' = ANY(ARRAY['First', 'Second']);
SELECT d FROM jsonthings WHERE d->'name' ?| ARRAY['First', 'Second'];
SELECT d FROM jsonthings WHERE d#>'{name}' ?| ARRAY['First','Second'];

e todos eles mostram uma varredura sequencial da tabela (estou usando enable_seqscan=falsepara forçar o uso do índice, se possível). Existe alguma maneira de reescrever a consulta para que ela use um índice? Estou ciente de que poderia fazer:

SELECT * FROM jsonthings WHERE d @> '{"name":"First"}' OR d @> '{"name":"Second"}';

mas então eu tenho uma consulta de comprimento variável e estou passando por JDBC, então perderia os benefícios da consulta sendo um PreparedStatement.

Também estou interessado em ver uma consulta semelhante em qualquer um dos vários itens da tagschave, por exemplo:

SELECT d FROM jsonthings WHERE d @> '{"tags":["foo"]}' OR d @> '{"tags":["bar"]}';

mas usando um ARRAYem vez de várias condições e usando um índice.

Isso está no PostgreSql 9.4.

postgresql json

2 respostas

  1. Esta é uma resposta à resposta fornecida por Mladen. Não tenho reputação suficiente para deixar um comentário, mas queria responder porque parece que a consulta pode estar incorreta e estava me confundindo e pode confundir outras pessoas no futuro.

    Você menciona usar:

    SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';

    Para recuperar quaisquer entradas que tenham Firstou Secondcomo nome, no entanto, isso não parece funcionar para mim em PostgreSQL 9.4.4:

    SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';
 d
---
(0 rows)

    Parece que a consulta acima está tentando recuperar entradas onde o nameatributo contém o array ["First", "Second"].

    Se eu criar tal entrada:

    INSERT INTO jsonthings VALUES ('{"name":["First", "Second"],"tags":["baz"]}');

    E então tente a consulta novamente, ela retorna um resultado:

    SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';
d
------------------------------------------------
{"name": ["First", "Second"], "tags": ["baz"]}
(1 row)

    No entanto, isso é diferente da pergunta feita pelo postador original, que era como usar um índice ao consultar entradas em que o nameatributo era First ou Second :

    SELECT * FROM jsonthings WHERE d @> '{"name":"First"}' OR d @> '{"name":"Second"}';

    Eu queria fornecer isso aqui para que outras pessoas não pensem que é possível realizar uma consulta OR com JSON fornecendo "name": ["First", "Second"], pois é enganoso.

    • 6
  2. Best Answer

    De docs ( http://www.postgresql.org/docs/9.4/static/datatype-json.html ) tente usar o índice de expressão:

    CREATE INDEX idx_jsonthings_names ON jsonthings USING gin ((d -> 'name'));
SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';
    • 3

relate perguntas