Index for finding an element in a JSON array

Question

I have a table that looks like this:

CREATE TABLE tracks (id SERIAL, artists JSON);

INSERT INTO tracks (id, artists) 
  VALUES (1, '[{"name": "blink-182"}]');

INSERT INTO tracks (id, artists) 
  VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]');

There's several other columns that aren't relevant to this question. There's a reason to have them stored as JSON.

What I'm trying to do is lookup a track that has a specific artist name (exact match).

I'm using this query:

SELECT * FROM tracks 
  WHERE 'ARTIST NAME' IN
    (SELECT value->>'name' FROM json_array_elements(artists))

for example

SELECT * FROM tracks
  WHERE 'The Dirty Heads' IN 
    (SELECT value->>'name' FROM json_array_elements(artists))

However, this does a full table scan, and it isn't very fast. I tried creating a GIN index using a function names_as_array(artists), and used 'ARTIST NAME' = ANY names_as_array(artists), however the index isn't used and the query is actually significantly slower.

Answer 1

jsonb in Postgres 9.4+

With the new binary JSON data type jsonb, Postgres 9.4 introduced largely improved index options. You can now have a GIN index on a jsonb array directly:

CREATE TABLE tracks (id serial, artists jsonb);
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);

No need for a function to convert the array. This would support a query:

SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';

@> being the new jsonb "contains" operator, which can use the GIN index. (Not for type json, only jsonb!)

Or you use the more specialized, non-default GIN operator class jsonb_path_ops for the index:

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (artists jsonb_path_ops);

Same query.

Currently jsonb_path_ops only supports the @> operator. But it's typically much smaller and faster. There are more index options, details in the manual.

---

If artists only holds names as displayed in the example, it would be more efficient to store a less redundant JSON value to begin with: just the values as text primitives and the redundant key can be in the column name.

Note the difference between JSON objects and primitive types:

• Using indexes in json array in PostgreSQL

CREATE TABLE tracks (id serial, artistnames jsonb);
INSERT INTO tracks  VALUES (2, '["The Dirty Heads", "Louis Richards"]');

CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);

Query:

SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';

? does not work for object values, just keys and array elements.
Or (more efficient if names are repeated often):

CREATE INDEX tracks_artistnames_gin_idx ON tracks
USING  gin (artistnames jsonb_path_ops);

Query:

SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;

json in Postgres 9.3+

This should work with an IMMUTABLE function:

CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';

Create this functional index:

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (json2arr(artists, 'name'));

And use a query like this. The expression in the WHERE clause has to match the one in the index:

SELECT * FROM tracks
WHERE  '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));

Updated with feedback in comments. We need to use array operators to support the GIN index.
The "is contained by" operator <@ in this case.

Notes on function volatility

You can declare your function IMMUTABLE even if json_array_elements() isn't wasn't.
Most JSON functions used to be only STABLE, not IMMUTABLE. There was a discussion on the hackers list to change that. Most are IMMUTABLE now. Check with:

SELECT p.proname, p.provolatile
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'pg_catalog'
AND    p.proname ~~* '%json%';

Functional indexes only work with IMMUTABLE functions.