CODEWITHSUNDEEP
pivotdistinctpresto
user21479
user21479

Reputation: 1189

presto: convert array to rows?

i have a table with array columns all_available_tags and used_tags.

example row1: 
all_available_tags:A,B,C,D
used_tags:A,B

example row2: 
all_available_tags:B,C,D,E,F
used_tags:F

I want to get distinct set of all_available_tags from all rows and do except the set with all used_tags from all rows. from example above, all_available_tags of all rows would be A,B,C,D,E,F and all used_tags would be A,B,F. the end result i am looking for is C,D,E

I think i need to somehow pivot the table but there could be 100s of different tags, so it is not practical to list out everyone of them. is there a good way to do this?

Upvotes: 6

Views: 14259

Answers (1)

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

You can try:

with tags(at, ut) as
( 
select "A,B,C,D", "A,B"
union all
select "B,C,D,E,F", "F"
)
select splitat
from tags
cross join unnest(split(at, ",")) as t1 splitat
except
select splitut
from tags
cross join unnest(split(ut, ",")) as t2 splitut

Upvotes: 2

Related Questions