jeudi 22 janvier 2015

PostgreSql JSONB SELECT against multiple values


I have a very simple JSON table which I populate with some sample data:



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);


And am attempting to use the index when running a SELECT. A simple SELECT to obtain the rows where the value is a single item works just fine:



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


But when attempting to run a query which matches more than one value of name I can't find out how to use the index. I've tried:



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'];


and all of them show a sequential scan of the table (I'm using enable_seqscan=false to force index use if possible). Is there some way I can rewrite the query so that it uses an index? I'm aware that I could do:



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


but then I have a variable-length query and I'm going through JDBC so would then lose the benefits of the query being a PreparedStatement.


I'm also interested in seeing a similar query against any of a number of items in the tags key, e.g.:



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


but using an ARRAY rather than multiple conditions and using an index.


This is on PostgreSql 9.4.





Aucun commentaire:

Enregistrer un commentaire