How to get intersection of two arrays in BigQuery

Question

I have data like:

id  col1     col2
-----------------
1   [1,2]    [2,3]
2   [4,4,6]  [6,7]

and I want to have data like:

id  col3
---------
1   [2]
2   [6]

Any smart solutions for this?

Answer 1

You can use INTERSECT DISTINCT

-- build example table
WITH example as (
  SELECT
  * FROM UNNEST([
      STRUCT([1,2] as col1, [2,3] as col2),
      STRUCT([4,4,6],[6,7])
    ])
  )

-- INTERSECT per row on two arrays
SELECT
  ARRAY(SELECT * FROM example.col1
    INTERSECT DISTINCT
    (SELECT * FROM example.col2)
  ) AS result
FROM example

Answer 2

Sorry, I solved by myself:

#standardSQL
CREATE TEMPORARY FUNCTION intersection(x ARRAY<INT64>, y ARRAY<INT64>)
RETURNS INT64
LANGUAGE js AS """
  var res =  x.filter(value => -1 !== y.indexOf(value));
  return res;
;
""";

Any other smarter idea is welcome! Thanks.