PostgreSql JSONB SELECT against multiple values

Question

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.

Answer 1

From docs (http://www.postgresql.org/docs/9.4/static/datatype-json.html) try to use expression index:

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

Answer 2

This is a response to the answer provided by Mladen. I don't have enough reputation to leave a comment, but I wanted to respond because it looks like the query may be incorrect, and was confusing me, and may cause other people to be confused in the future.

You mention using:

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

To retrieve any entries that have either First or Second as the name, however, this doesn't seem to work for me on PostgreSQL 9.4.4:

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

It seems the above query is attempting to retrieve entries where the name attribute contains the array ["First", "Second"].

If I create such an entry:

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

And then try the query again, it returns a result:

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

However, this is different from the question asked by the original poster, which was how to use an index when querying entries where the name attribute was either First or Second:

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

I wanted to provide this here so other people don't think it's possible to perform an OR query with JSON by providing "name": ["First", "Second"], since it's misleading.