Search code examples
sqlgoogle-bigquery

How to get intersection of two arrays in BigQuery

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?

Solution

  • 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