title: Join tables using a value inside a JSONB column
body: <p>There are two tables:</p>
  
  <p>Authorized Contacts (<code>auth_contacts</code>):</p>
  
  <pre><code>(
  userid varchar
  contacts jsonb
  )
  </code></pre>
  
  <p><code>contacts</code> contains an array of contacts with attributes <code>{contact_id, type}</code></p>
  
  <p><code>discussion</code>:</p>
  
  <pre><code>(
  contact_id varchar
  discussion_id varchar
  discussion_details jsonb
  )
  </code></pre>
  
  <p>The table <code>auth_contacts</code> has at least 100k records making it non JSONB type is not appropriate according as it would double or triple the amount of records.</p>
  
  <p>Sample data for <code>auth_contacts</code>:</p>
  
  <pre><code>userid  | contacts
  '11111' | '{"contact": [{"type": "type_a", "contact_id": "1-A-12"}
                        , {"type": "type_b", "contact_id": "1-A-13"}]}'
  </code></pre>
  
  <p><code>discussion</code> table has 5 million odd records.</p>
  
  <p>I want to join on <code>discussion.contact_id</code> (relational column) with contact id which a json object inside array of json objects in <code>auth_contacts.contacts</code>.</p>
  
  <p>One very crude way is:</p>
  
  <pre><code>SELECT *
  FROM discussion d 
  JOIN (SELECT userid, JSONB_OBJECT_KEYS(a.contacts) AS auth_contact
        FROM auth_contacts a) AS contacts
        ON (d.contact_id = contacts.auth_contact::text)
  </code></pre>
  
  <p>What this does is actually at runtime create (inner sql) userid vs contact id table (Which is what I was  avoiding and hence went for JSONB data type
  This query for a user with large records takes 26 + seconds which is not all good.
  Tried a few other ways: <a href="https://stackoverflow.com/questions/26520150/postgresql-9-4-aggregate-join-table-on-json-field-id-inside-array">PostgreSQL 9.4: Aggregate / Join table on JSON field id inside array</a></p>
  
  <p>But there should be a cleaner and better way which would be as simple as 
  JOIN <code>d.contact_id = contacts -&gt; contact -&gt; contact_id?</code>
  When I try this, it doesn't yield any results.</p>
  
  <p>When searching the net this seems to be a pretty cumbersome task?</p>