How to implement ARRAY_INTERSECT with an unknown/random of number of arrays

Help Center
1

The Problem

The ARRAY_INTERSECT function requires a constant number of arrays - for example, a column of arrays.

The Solution

To implement ARRAY_INTERSECT on an unknown or random number of arrays, you can use a Lambda function inside of another Lambda function.

  1. Use ARRAY_AGG to bring the arrays per group into an array of arrays
  2. Split the array of arrays into `first_array` and `rest_arrays`, using ELEMENT_AT and ARRAY_SLICE array functions.
  3. For every element in the first array:
    1. Check if remaining arrays CONTAINS it - if not, FILTER out

Example Syntax

WITH base AS (SELECT ['a','b','c'] AS arrUNION ALL SELECT ['a','b'] AS arrUNION ALLSELECT ['a'] AS arr),base2 AS (SELECT ELEMENT_AT(ARRAY_AGG(arr),1) AS first_array, -- use ARRAY_AGG to bring the arrays per group into an array of arraysSLICE(ARRAY_AGG(arr), 2) AS rest_arrays -- use ARRAY_AGG to bring the arrays per group into an array of arrays and extract the rest of the arraysFROM base)SELECT-- inner lambda (ALL MATCH) , outer lambda (FILTER)FILTER(y -> ALL_MATCH(x -> CONTAINS(x,y), rest_arrays), first_array)FROM base2;