5

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?

user21479
  • 1,179
  • 2
  • 13
  • 21

1 Answers1

2

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
Neeraj Agarwal
  • 1,059
  • 6
  • 5