Postgres accessing JSONB elements in efficient way

Question

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

pozs · Accepted Answer · 2017-04-20 09:05:28Z

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

http://rextester.com/ZMCUME18642

Collectives™ on Stack Overflow

Postgres accessing JSONB elements in efficient way

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related