title: postgres: How to count distinct elements in array columns given a condition
body: <p>I have a table like the following:</p>
  
  <pre><code>ID     array1           array2
  7333 | {615593}        | {NULL}
  7333 | {2013682}       | {NULL}
  7333 | {26573,1508291} | {NULL}
  7333 | {1457957}       | {NULL}
  7333 | {NULL}          | {1063105}
  7333 | {NULL}          | {107978,408431}
  </code></pre>
  
  <p>I am looking to query all unique items for array1 and array2 for each edge_id.  For example</p>
  
  <pre><code>ID   array1_distinct    array2_distinct
  7333 5                  3
  </code></pre>
  
  <p>I've tried something like the following</p>
  
  <pre><code>SELECT 
    id, 
    array1_count 
  FROM (
    SELECT id,
    COUNT(DISTINCT((CASE WHEN array1 is not null THEN (select unnest(array1)b) END)) as array1_count
    FROM mytable
    GROUP BY id
  ) totals
  limit 1
  </code></pre>
  
  <p>I always get errors like "more than one row returned by a subquery used as an expression" and "set-valued function called in context that cannot accept a set" when trying to count elements in arrays when using unnest.</p>