title: PostgreSQL search/index (GIN index?) across two columns/arrays
body: <p>Let's say we have a PostgreSQL table <code>contacts</code>, each record having a bunch of labeled email addresses (pairs of labels and emails) &mdash; one of which is the "primary".</p>
  
  <p>This is stored like:</p>
  
  <ul>
  <li><code>id</code> primary key</li>
  <li><code>email</code> text</li>
  <li><code>email_label</code> text</li>
  <li><code>metadata</code> jsonb
  
  <ul>
  <li><code>emails</code> array</li>
  <li><code>email</code> text</li>
  <li><code>label</code> text</li>
  </ul></li>
  </ul>
  
  <p>For example, a record might look something like:</p>
  
  <pre><code>id: 1
  email: 'a@a.com'
  email_label: 'a'
  metadata: {
              "emails": [
                          {
                            "email": "b@b.com",
                            "label": "b"
                          },
                          {
                            "email": "c@c.com",
                            "label": "c"
                          }
                        ]
            }
  </code></pre>
  
  <p>Given this storage pattern, we want to be able to find a record by any of its email addresses.</p>
  
  <p>The naive query would look like:</p>
  
  <pre><code>SELECT id
  FROM contacts
  WHERE
    email = 'my@email.com' OR
    metadata -&gt; 'emails' @&gt; '[{"email": "my@email.com"}]'
  </code></pre>
  
  <p><strong>Is there any way to create an index that speeds up this operation significantly?</strong> It would need to update automatically in response to changes to the records, and ideally index across both the text column and the nested JSONB column.</p>
  
  <p>The specific use case here would be able to do lookups by email address efficiently and quickly, without overhauling this structure or creating a new relational table.</p>
  
  <p>I believe the solution involves using a GIN index and <a href="https://dba.stackexchange.com/questions/127689/how-to-get-particular-object-from-jsonb-array-in-postgresql">this question mentions jsonb_path_ops</a> but I'm not sure how to wrangle all the pieces together.</p>