We have table:

create table last_games(id int not null primary key, name text not null, json_value jsonb);

And filled with next values:

insert into last_games values 
(1, 'Tournament1', '{"places": ["2","1"]}'::jsonb),
(2, 'Tournament2', '{"places": ["4","2"]}'::jsonb),
(3, 'Tournament3', '{"places": "2"}'::jsonb);

As you can see places is array in two rows and string in another one. Then add GIN index.

create index idx_gin_places on "last_games" using GIN (("json_value" -> 'places'));

If you make query then

select * from last_games lg where lg.json_value -> 'places' ? '2';

you will get all 3 rows. But what about index? It's ok that part value of key is array and other is string?

  • 1
    Check the execution plan - but you will need much more than only 3 rows to see if it's working. – a_horse_with_no_name Jan 17 at 10:25
  • @a_horse_with_no_name First turn off enable_seqscan, then 3 rows is enough to test with. (And yes, it uses the index) – jjanes Jan 17 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.