title: Index for finding an element in a JSON array
body: <p>I have a table that looks like this:</p>
  
  <pre><code>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"}]');
  </code></pre>
  
  <p>There's several other columns that aren't relevant to this question. There's a reason to have them stored as JSON. </p>
  
  <p>What I'm trying to do is lookup a track that has a specific <em>artist name</em> (exact match).</p>
  
  <p>I'm using this query:</p>
  
  <pre><code>SELECT * FROM tracks 
    WHERE 'ARTIST NAME' IN
      (SELECT value-&gt;&gt;'name' FROM json_array_elements(artists))
  </code></pre>
  
  <p>for example</p>
  
  <pre><code>SELECT * FROM tracks
    WHERE 'The Dirty Heads' IN 
      (SELECT value-&gt;&gt;'name' FROM json_array_elements(artists))
  </code></pre>
  
  <p>However, this does a full table scan, and it isn't very fast. I tried creating a GIN index using a function <code>names_as_array(artists)</code>, and used <code>'ARTIST NAME' = ANY names_as_array(artists)</code>, however the index isn't used and the query is actually significantly slower.</p>