title: Postgres jsonb array join
body: <p>I have a jsonb document in a table.  This document has an array of cameraIds in the document.  I am trying to join this data with the cameras table that is a normal table where cameraId is a column, and return unique rows from the table with the jsonb column (why I am using a group by in my query).</p>
  <p>Any advice on how to optimize this query for performance would be greatly appreciated.</p>
  <p>JSONB Col Example:</p>
  <pre><code>{
    &quot;date&quot;: {
      &quot;end&quot;: &quot;2018-11-02T22:00:00.000Z&quot;, 
      &quot;start&quot;: &quot;2018-11-02T14:30:00.000Z&quot;
    }, 
    &quot;cameraIds&quot;: [100, 101], 
    &quot;networkId&quot;: 5, 
    &quot;filters&quot;: [], 
    &quot;includeUnprocessed&quot;: true, 
    &quot;reason&quot;: &quot;some reason&quot;, 
    &quot;vehicleFilter&quot;: { 
      &quot;bodyInfo&quot;: &quot;something&quot;, 
      &quot;lpInfo&quot;: &quot;something&quot;
    }
  }
  
  </code></pre>
  <p>Query:</p>
  <pre><code>select ssr.id,
                  a.name                                               as user_name,
                  ssr.start_date,
                  ssr.end_date,
                  ssr.created_at,
                  ssr.payload -&gt; 'filters'                       as pretty_filters,
                  ssr.payload -&gt; 'reason'                              as reason,
                  ssr.payload -&gt; 'includePlates'                as include_plates,
                  ssr.payload -&gt; 'vehicleFilter' -&gt; 'bodyInfo'         as vbf,
                  ssr.payload -&gt; 'vehicleFilter' -&gt; 'lpInfo' as lpInfo,
                  array_agg(n.name) filter (where n.organization_id = ${orgId})  as network_names,
                  array_agg(c.name) filter (where n.organization_id = ${orgId})  as camera_names
  from
      ssr
      cross join jsonb_array_elements(ssr.payload -&gt; 'cameraIds') camera_id
           inner join cameras as c on c.id = camera_id::int
           inner join networks as n on n.id = c.network_id
           inner join accounts as a on ssr.account_id = a.id
  where n.organization_id = ${someId}
  and ssr.created_at between ${startDate} and ${endDat}
  group by 1,2,3,4,5,6,7,8,9,10
  order BY ssr.created_at desc
      OFFSET 0
  LIMIT 25;
  </code></pre>