Union of arrays as aggregate function

I have the following input:

name  | count | options
-----------------------
user1 | 3     | ['option1', 'option2']
user1 | 12    | ['option2', 'option3']
user2 | 2     | ['option1', 'option3']
user2 | 1     | []

I want the following output:

name  | count | options
-----------------------
user1 | 12    | ['option1', 'option2', 'option3']
user2 | 2     | ['option1', 'option3']

I am grouping by name. For each group, the count should be aggregated as the max and the options should be aggregated as the union. I am having troubles figuring out how do the the latter.

Currently, I have this query:

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, max(count)
from data
group by name

http://rextester.com/YTZ45626

I know this can be easily done by defining a custom aggregate function, but I want to do this via a query. I understand the basics of unnest() the array (and array_agg() the results later on), but cannot figure out how to inject this in my query.

Answer

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)