title: SQL filtering rows using WHERE clause with array_agg function and joins
body: <p>My current query:</p>
  
  <pre><code>SELECT 
      i.ID AS interview_id, i.board, i.time_taken, i.notes, i.interview_date,
      u.ID AS user_id, u.first_name, u.last_name, u.state, u.district, u.optional, u.is_interview_user_only, 
      COALESCE((SELECT array_agg(DISTINCT uj.job_name) as jobs FROM user_jobs uj WHERE uj.user_id = u.id), '{}') as jobs
      FROM interview i
      JOIN users u ON i.user_id = u.id 
      GROUP BY u.id, i.id;
  </code></pre>
  
  <p>I want to implement filters for the interviews by "checking if a certain Job is in the array_agg of that interview row."</p>
  
  <p>Current output:</p>
  
  <pre><code>interview_id | time_taken | ... | jobs
  1001         | 25         | ... | {CEO, Product Manager}
  1002         | 20         | ... | {Customer Care, Hospitality}
  1003         | 40         | ... | {CEO, CFO}
  1004         | 35         | ... | {Army Official, Sales Manager}
  </code></pre>
  
  <p>Output I want: When I mention Jobs having "CEO" as a filter/criteria</p>
  
  <pre><code>interview_id | time_taken | ... | jobs
  1001         | 25         | ... | {CEO, Product Manager}
  1003         | 40         | ... | {CEO, CFO}
  </code></pre>
  
  <p>to return rows only having "CEO" as one of the value in jobs array aggregate.</p>
  
  <p>I am not sure how to use the WHERE or HAVING or IN clause with the above query so as to filter the rows based on results from the subquery aggregate array.</p>
  
  <p>Is this possible in a single query where joins and aggregates are present?</p>
  
  <p>If not, how else can I make this possible?</p>
  
  <p>I want to implement this statement for applying sorts and filters options selected from Frontend.</p>
  
  <p>Database: PostgreSQL<br />
  Environment: Node using Express and node-pg package.<br />
  Frontend: Vue</p>