9

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.

Improve this question

2 Answers 2

Reset to default
10

The following seems to do the trick:

SELECT DISTINCT(unnest(tbl.ar)) FROM tbl
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

FWIW in my setup, tbl is not necessary in tbl.ar, i.e., this works for me: SELECT DISTINCT(unnest(ar)) FROM tbl
@sambecker yeah, you're right, tbl in tbl.ar is optional unless it's needed to resolve ambiguity in some more complex query.
2

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} |
Improve this answer

7 Comments

Sure, the order doesn´t matter but could you explain me your code if the table name is car and the column name is cityfrom.
@JonathanPrieto: just replace mytable with car and ar with cityfrom.
Worked Fine Thank you!
@JonathanPrieto: the where goes after the from clause (which itself includes the lateral join)
Oh I just understood thank you! Here is the answer: Select array_agg(distinct u.val) new_usoauto from autos t cross join lateral unnest(t.usoauto) as u(val) where 'particular'= ANY(usoauto);
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.