PostgreSQL JSONB GIN Index multi condition-CodePudding

I'm using PostgreSQL 13 and I have a table that has a JSONB column and GIN index.

My question is to find a better way for the following query. I have multiple IDs and sometimes I need to find them.

My Schema:

id  timestamp   data    
404599  2022-01-01 00:15:02.566 {"env": "worker", "id":1, "name": "foo", "lang": "en"}
404600  2022-01-01 00:15:02.685 {"env": "worker", "id":2, "name": "foo", "lang": "fr"}
404601  2022-01-01 00:15:02.808 {"env": "worker", "id":3, "name": "foo", "lang": "ru"}
404602  2022-01-01 00:15:03.023 {"env": "worker", "id":3, "name": "foo", "lang": "de"}
404603  2022-01-01 00:15:03.170 {"env": "worker", "id":4, "name": "foo", "lang": "tr"}

My Query:

select * from foo where data @>  '{"id": 1}' or data @> '{"id": 2}' or data @> '{"id": 3}';

I want to use the GIN index for the "data" column but I couldn't find any better way that looks like the "in" operator.

CodePudding user response：

That is the best way to query with a GIN index on the JSON column.

If all your queries look like that, using a b-tree index would be more efficient:

CREATE INDEX ON foo ((data ->> 'id'));

SELECT * FROM foo WHERE data ->> 'id' IN (1, 2, 3);