How to check if an array contains any elements of another array-CodePudding

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?

CodePudding user response：

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