Union of arrays as aggregate function
You can use an implicit lateral join using unnest(options)
in the FROM
list, and then using array_agg(distinct v)
to create an array with the options:
with data(name, count, options) as (
select 'user1', 12, array['option1', 'option2']::text[]
union all
select 'user1', 12, array['option2', 'option3']::text[]
union all
select 'user2', 2, array['option1', 'option3']::text[]
union all
select 'user2', 1, array[]::text[]
)
select name, array_agg(distinct v) -- the 'v' here refers to the 'f(v)' alias below
from data, unnest(options) f(v)
group by name;
┌───────┬───────────────────────────┐
│ name │ array_agg │
├───────┼───────────────────────────┤
│ user1 │ {option1,option2,option3} │
│ user2 │ {option1,option3} │
└───────┴───────────────────────────┘
(2 rows)