How to declare a list/array/struct type variable in BigQuery

Question

How can I declare a list type variable in BigQuery so that I can use it in a where clause? I have this code

WITH
  subquery AS (
  SELECT
    1 AS col1
  UNION ALL
  SELECT
    2 AS col1
  UNION ALL
  SELECT
    3 AS col1 )

SELECT
  col1
FROM
  subquery
WHERE
  col1 IN (1, 2)

instead I would like to get to the point with the variable in the query

DECLARE list ARRAY;
SET list = (1,2);

WITH
  subquery AS (
  SELECT
    1 AS col1
  UNION ALL
  SELECT
    2 AS col1
  UNION ALL
  SELECT
    3 AS col1 )

SELECT
  col1
FROM
  subquery
WHERE
  col1 IN list

I have tried DECLARE list STRUCT [less than] int64,int64 [greater than] which it doesn't accept

Answer 1

Try the following code:

DECLARE list ARRAY <INT64>;
SET list = [1,2];

WITH
  subquery AS (
  SELECT
    1 AS col1
  UNION ALL
  SELECT
    2 AS col1
  UNION ALL
  SELECT
    3 AS col1 )

SELECT
  col1
FROM
  subquery
WHERE
  col1 IN UNNEST(list)