Create multiple column array in Google bigquery

Question

Im trying to set the result of a query as an array. I can do this fine with only one column returned using the code below, but i get errors as soon as i try to add another column. How do i get multiple columns and store them in an array?

Thanks.

DECLARE my_array ARRAY<string>;

SET my_array = (
  SELECT ARRAY_AGG(value_1,value2)
  FROM `project.dataset.table`
  WHERE somthing = 'somthing'
);

Mikhail Berlyant · Accepted Answer · 2021-01-21 04:14:14Z

2

Try below

DECLARE my_array ARRAY<STRUCT<value_1 STRING,value2 STRING>>;

SET my_array = (
  SELECT ARRAY_AGG(STRUCT(value_1,value2))
  FROM `project.dataset.table`
  WHERE somthing = 'somthing'
);

edited Jan 21, 2021 at 4:14

answered Jan 21, 2021 at 3:53

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

minobu Over a year ago

ahh thank you! I had tried STRUCT but was not declaring the array correctly.

Collectives™ on Stack Overflow

Create multiple column array in Google bigquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related