sql - Using indexes in json array in PostgreSQL

Question

Referring to the original stackoverflow question, I am trying to apply gin indexes to keys in objects of an array in Postgres 9.4 but I'm not getting the results as stated in the first answer.

Can you please rectify the error?

The steps I followed have been written below.

Part 1: Creating table and indexes

CREATE TABLE tracks (id serial, artists jsonb);
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);
INSERT INTO tracks (id, artists) VALUES (1, '[{"name": "blink-182"}]');
INSERT INTO tracks (id, artists) VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]');

Part 2: Query

SELECT * FROM tracks WHERE artists @> '{"name": "The Dirty Heads"}';
 id | artists 
----+---------
(0 rows)

This query gives empty results.
I also tried to use jsonb_path_ops GIN indexes.

Alternative index and query:

DROP INDEX tracks_artists_gin_idx;
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING  gin (artists jsonb_path_ops);
SELECT * FROM tracks WHERE artists @> '{"name": "The Dirty Heads"}';
 id | artists 
----+---------
(0 rows)

Answer 1

This specific jsonb example from the original answer was missing the array layer [] around the non-primitive object for the containment query. It has since been fixed.

The behavior documented for PostgreSQL 9.4.x jsonb Containment and Existence states:

The general principle is that the contained object must match the containing object as to structure and data contents

...

As a special exception to the general principle that the structures must match, an array may contain a primitive value

The special exception allows us to do the following:

CREATE TABLE tracks (id serial, artistnames jsonb);
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);
INSERT INTO tracks (id, artists) VALUES (1, '["blink-182"]');
INSERT INTO tracks (id, artists) VALUES (2, '["The Dirty Heads", "Louis Richards"]');

We can query for containment using the general principle:

SELECT * FROM tracks WHERE artistnames @> '["The Dirty Heads"]';
 id |              artistnames              
----+---------------------------------------
  2 | ["The Dirty Heads", "Louis Richards"]
(1 row)

We can also query for containment using the special exception since the array contains primitive types:

SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"';
 id |              artistnames              
----+---------------------------------------
  2 | ["The Dirty Heads", "Louis Richards"]
(1 row)

There are 4 primitive types that allow containment and existence queries on arrays to work:

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

Since the example you mentioned in your question is dealing with objects nested inside an array, we don't qualify for the special exception mentioned above:

CREATE TABLE tracks (id serial, artists jsonb);
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);
INSERT INTO tracks (id, artists) VALUES (1, '[{"name": "blink-182"}]');
INSERT INTO tracks (id, artists) VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]');

We can query for containment using the general principle:

SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';
 id |                          artists                          
----+-----------------------------------------------------------
  2 | [{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]
(1 row)

Objects are not considered a primitive type, so the following query for containment does not qualify for the special exception and therefore does not work:

SELECT * FROM tracks WHERE artists @> '{"name": "The Dirty Heads"}';
 id | artists 
----+---------
(0 rows)