current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 13 down vote favorite
13

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.

share|improve this question
    
I've made a follow up question based on this one: dba.stackexchange.com/questions/71546/… –  Ken Li Jul 15 at 11:37

1 Answer 1

up vote 12 down vote accepted

This should work with an IMMUTABLE function:

CREATE OR REPLACE FUNCTION json_val_arr(_j json, _key text)
  RETURNS text[] AS
$$
SELECT array_agg(elem->>_key)
FROM   json_array_elements(_j) AS x(elem)
$$
  LANGUAGE sql IMMUTABLE;

Create this functional index:

CREATE INDEX tracks_artists_gin_idx ON tracks
USING GIN (json_val_arr(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  '{"ARTIST NAME"}'::text[] <@ (json_val_arr(artists, 'name'));

Requires Postgres 9.3.

Updated with feedback in comments. My oversight was that we need to use array operators in order for Postgres to use the GIN index. The operator <@ ("is contained by") in this case.

Notes on function volatility

You can declare your function IMMUTABLE even if json_array_elements() isn't. (You'd better check why, if that's the case!)
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.

share|improve this answer
1  
This doesn't work because returning SETOF can't be used in an index. Removing that, I can create the index, however it isn't used by the query planner. Also, both json_array_elements and array_agg are IMMUTABLE –  JeffS Aug 23 '13 at 17:21
    
@JeffS: SETOF was an artifact that shouldn't have been there. The function returns a single array. I wonder why the index is not used. Can be for a number of reasons. It's a pity I can't test right now. If all else fails, you could materialize the array using the new MATERIALIZED VIEW feature of 9.3 and base the index on that, or even create a 1:n sub-table with one row per json value and a plain b-tree index, kept current by triggers in a similar fashion. –  Erwin Brandstetter Aug 23 '13 at 18:25
1  
We were both close with that index. I updated your answer to use the @> instead of ANY, which will use the index. –  JeffS Aug 23 '13 at 20:02
    
@JeffS: Right! That was it! Your edit got rejected, but I re-applied it. I have written several related answers here and on dba.SE. –  Erwin Brandstetter Aug 23 '13 at 20:45
3  
Is it just me or should it be <@ instead of @> ?? Your code example '{"ARTIST"}' @> '{"ARTIST","OTHER"}' returns false. –  Peteris Sep 14 '13 at 10:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.