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 request all unique elements for array1 and array2 for each edge_id. for instance
ID array1_distinct array2_distinct
7333 5 3
I 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 such as "more than one row returned by a subquery used as an expression", and "set-valued function called in a context that cannot accept a set" when trying to count elements in arrays when using unsest.
source
share