Arraying two array rows and display only distinct values

Question

I want to aggregate two array rows. For example, in the table below, colour column already shows the values in array format.

id	colour
1	[[Red,Blue,Yellow]]
2	[[Green]]
1	[[White,Blue]]
2	[[Green,Black]]

By aggregating on id and distinct-selecting the colour column, I want to create a result like below.

id	colour
1	[[Red,Blue,Yellow,White]]
2	[[Green,Black]]

How do I achieve this?

Wich DB Product are you using?

gonzalorf
– gonzalorf

2023-05-19 15:36:10 +00:00
Commented May 19, 2023 at 15:36 — gonzalorf
– gonzalorf, Commented May 19, 2023 at 15:36
It's Amazon Athena

user16462786
– user16462786

2023-05-19 15:42:19 +00:00
Commented May 19, 2023 at 15:42 — user16462786
– user16462786, Commented May 19, 2023 at 15:42

GMB · Accepted Answer · 2023-05-22 14:45:46Z

3

Presto has lots of array functions. We can try:

select id, array_distinct(flatten(array_agg(colour))) as colours
from mytable
group by id

This assumes that each array contains a list of scalars, so something like: [Red,Blue,Yellow] - not a one element sub-array (as shown in your sample data like [[Red,Blue,Yellow]]).

edited May 22, 2023 at 14:45

answered May 19, 2023 at 15:42

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Arraying two array rows and display only distinct values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related