I have batch_table table, which contain batchid type of serial int and data type of JSONB, I indexed data column using GIN,
batchid | data
---------------------------------------------
1 | [{"year":2000,"productid":[21, 32, 5]}]
2 | [{"year":2001,"productid":[21, 39, 5]},{"year":2000,"productid":[1, 25, 5]}]
3 | NULL
4. | [{"year": 2000,"productid":[5]}
Now I want to get batchid by using following requirements
1. year = 2000 & productid= 5
2. year = 2000 & productid= (21 or 5)
3. year = 2000 & productid= (21 & 5)
and I tried this
SELECT batchid FROM batch_table WHERE (data->>'year')::int = 2000 AND (data->>'productid')::int = 5;
with AND & OR for other queries
You can use the containment operator @> to search in jsonb (this can even use your index):
1.
select *
from batch_table
where data @> '[{"year":2000,"productid":[5]}]';
2.
select *
from batch_table
where data @> '[{"year":2000,"productid":[21]}]'
or data @> '[{"year":2000,"productid":[5]}]';
3.
Depending on your needs, you can use one of these:
These will select rows, where year=2000 with productid=21 are in the same object and year=2000 with productid=5 are in the same object (but these objects can be different ones).
select *
from batch_table
where data @> '[{"year":2000,"productid":[21]}]'
and data @> '[{"year":2000,"productid":[5]}]';
select *
from batch_table
where data @> '[{"year":2000,"productid":[21]},{"year":2000,"productid":[5]}]';
This will select rows, where year=2000 with productid=21 are in the same object as well as productid=5
select *
from batch_table
where data @> '[{"year":2000,"productid":[21, 5]}]';