How to get unique values from a column of Arrays PostgreSQL

Question

I am trying to extract DISTINCT value of a column of arrays.

For example, If I have two rows:

{jonathan,michelle}
{jonathan,michael}

The output should be:

{jonathan,michelle,michael}

The output can be an array or a "virtual column" it is not a problem.

Answer 1

You can unnest and aggregate back, ignoring duplicates:

select array_agg(distinct u.val) new_ar
from mytable t
cross join lateral unnest(t.ar) as u(val)

Note that this does not guarantee the order in which elements will appear in the final array (there are options, but you did not specify what you wanted in that regard).

Demo on DB Fiddle:

| new_ar                      |
| :-------------------------- |
| {jonathan,michael,michelle} |