PostgreSql JSONB SELECT against multiple values
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"]}';
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.