BigQuery array intersection across all rows

Question

I have the following data:

col1
[1,2,3]
[1,2]
[2,3]

I want the following output

ans
[2]

In other words, I want the intersection of arrays in all rows. Any pointers?

Answer 1

Consider below approach

select array_agg(item) ans from (
  select distinct item
  from your_table t, t.col1 item
  qualify count(distinct format('%t', col1)) over(partition by item) 
    = count(distinct format('%t', col1)) over()
)