Here I've an array ["chair","desk","charger"], I'd like to check if a different array contains any the elements in the first array.
Example:
["chair","desk"] //returns true;
["screen","charger","computer"] //returns true;
["calendar", "screen"] //returns false;
How could this be done in Presto SQL?
Check cardinality of array returned by array_intersect(x, y) function.
Demo:
with mydata as (
select 1 id, array['chair','desk'] as myarray union all
select 2 id, array['screen','charger','computer'] union all
select 3 id, array['calendar', 'screen']
)
select id,
cardinality(array_intersect(myarray, array['chair','desk','charger']))>0 as contains_flag
from mydata
order by id
Result:
id contains_flag
1 TRUE
2 TRUE
3 FALSE
