I have a table like the following:
ID array1 array2
7333 | {615593} | {NULL}
7333 | {2013682} | {NULL}
7333 | {26573,1508291} | {NULL}
7333 | {1457957} | {NULL}
7333 | {NULL} | {1063105}
7333 | {NULL} | {107978,408431}
I am looking to query all unique items for array1 and array2 for each edge_id. For example
ID array1_distinct array2_distinct
7333 5 3
I've tried something like the following
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
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.