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

rmesteves · Accepted Answer · 2020-01-06 15:30:21Z

15

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)

answered Jan 6, 2020 at 15:30

rmesteves

4,1359 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user147529 Over a year ago

Yay thank you. I struggle to understand arrays and structs. Any good resources?

rmesteves Over a year ago

You can find useful information about it in the link bellow cloud.google.com/bigquery/docs/reference/standard-sql/…

FreshSnow · Accepted Answer · 2023-03-01 20:57:22Z

5

You can DECLARE an array and assign it values in one go, without using SET. Example:

DECLARE myArray ARRAY <STRING> DEFAULT ["FIRST", "SECOND", "THIRD"];

SELECT myValues
  FROM UNNEST(myArray) AS myValues

You may prefer a subquery, using WITH. Example:

WITH myArray AS (
  SELECT *
    FROM UNNEST(["FIRST", "SECOND", "THIRD"]) AS myValues
)
SELECT myValues
  FROM myArray

Both queries will provide the same result:

+----------+
| myValues |
+----------+
| FIRST    |
| SECOND   | 
| THIRD    |
+----------+

answered Mar 1, 2023 at 20:57

FreshSnow

4345 silver badges12 bronze badges

Comments

Sean · Accepted Answer · 2020-11-30 22:27:59Z

4

The following syntax also seems to work, if you really want to use DECLARE with a STRUCT type, or for other people who find this post:

DECLARE foo DEFAULT (SELECT AS STRUCT 2, 2, 2, 2);

answered Nov 30, 2020 at 22:27

Sean

4833 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related