sql - Using indexes in json array in PostgreSQL -

referring original stackoverflow question, trying apply gin indexes keys in objects of array in postgres 9.4 i'm not getting results stated in first answer.

can please rectify error?

the steps followed have been written below.

part 1: creating table , indexes

create table tracks (id serial, artists jsonb); create index tracks_artists_gin_idx on tracks using gin (artists); insert tracks (id, artists) values (1, '[{"name": "blink-182"}]'); insert tracks (id, artists) values (2, '[{"name": "the dirty heads"}, {"name": "louis richards"}]'); 

part 2: query

select * tracks artists @> '{"name": "the dirty heads"}';  id | artists  ----+--------- (0 rows) 

this query gives empty results.
tried use jsonb_path_ops gin indexes.

alternative index , query:

drop index tracks_artists_gin_idx; create index tracks_artistnames_gin_idx on tracks using  gin (artists jsonb_path_ops); select * tracks artists @> '{"name": "the dirty heads"}';  id | artists  ----+--------- (0 rows) 

this specific jsonb example original answer missing array layer [] around non-primitive object containment query. has since been fixed.

the behavior documented postgresql 9.4.x jsonb containment , existence states:

the general principle contained object must match containing object structure , data contents

...

as special exception general principle structures must match, array may contain primitive value

the special exception allows following:

create table tracks (id serial, artistnames jsonb); create index tracks_artistnames_gin_idx on tracks using gin (artistnames); insert tracks (id, artists) values (1, '["blink-182"]'); insert tracks (id, artists) values (2, '["the dirty heads", "louis richards"]'); 

we can query containment using general principle:

select * tracks artistnames @> '["the dirty heads"]';  id |              artistnames               ----+---------------------------------------   2 | ["the dirty heads", "louis richards"] (1 row) 

we can query containment using special exception since array contains primitive types:

select * tracks artistnames @> '"the dirty heads"';  id |              artistnames               ----+---------------------------------------   2 | ["the dirty heads", "louis richards"] (1 row) 

there 4 primitive types allow containment , existence queries on arrays work:

1. string
2. number
3. boolean
4. null

since example mentioned in question dealing objects nested inside array, don't qualify special exception mentioned above:

create table tracks (id serial, artists jsonb); create index tracks_artists_gin_idx on tracks using gin (artists); insert tracks (id, artists) values (1, '[{"name": "blink-182"}]'); insert tracks (id, artists) values (2, '[{"name": "the dirty heads"}, {"name": "louis richards"}]'); 

we can query containment using general principle:

select * tracks artists @> '[{"name": "the dirty heads"}]';  id |                          artists                           ----+-----------------------------------------------------------   2 | [{"name": "the dirty heads"}, {"name": "louis richards"}] (1 row) 

objects not considered primitive type, following query containment not qualify special exception , therefore not work:

select * tracks artists @> '{"name": "the dirty heads"}';  id | artists  ----+--------- (0 rows)