CODEWITHSUNDEEP
google-bigquery
heliomar_
heliomar_

Reputation: 1

How to check if an item of a list is in another item of list on Bigquery?

I have a column, like ['11999999999','12999999999','31999999999'] and anothher column, like ['5511777777777','5512888888888','5531999999999']. I want to do a CASE WHEN to return 1, if any item on the first column is in any item of the second column. How to do this?

Upvotes: 0

Views: 1220

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below approach

select *, if(exists ( 
    select * from t.col1 intersect distinct 
    select * from t.col2
  ), 1, 0) as has_overlap
from your_table t

if applied to sample data like in your question - output is

enter image description here

Upvotes: 1

Jaytiger
Jaytiger

Reputation: 12254

You can use JOIN to check an element exisits in both arrays.

 WITH sample AS (
   SELECT ['11999999999','12999999999','31999999999' ] col1,
          ['5511777777777','5512888888888','5531999999999', '11999999999'] col2
 )
 SELECT (SELECT 1 FROM UNNEST(col1) c1 JOIN UNNEST(col2) c2 ON c1 = c2) 
   FROM sample;
--or 
 SELECT (SELECT 1 FROM UNNEST(col1) c1, UNNEST(col2) c2 WHERE c1 = c2) 
   FROM sample;

Query results:
+-----+------+
| Row | f0_  |
+-----+------+
|   1 |  1   |
+-----+------+

Upvotes: 0

Pratik Patil
Pratik Patil

Reputation: 832

See if following helps:

with sample as (
select array_agg(col1) as col1, array_agg(col2) as col2  
from (
select '11999999999' as col1, '123345567' as col2 
union all 
select '12999999999' as col1 , '31999999999' as col2
union all 
select '31999999999' as col1  , '5512888888888' as col2 
)
)

select (case when array_length(array((SELECT * FROM UNNEST(sample.col1)) INTERSECT DISTINCT (( SELECT * FROM UNNEST(sample.col2))))) > 0 then true else false end) from sample

results => true (because 31999999999 from col1 is in col2 as well)

Upvotes: 0

Related Questions