Bigquery : how to declare an array variable and set data with a select statement?

Question

I am trying to declare an array variable on BigQuery but I don't manage to put a SQL statement in my variable. I couldn't find any topic about that.

I want to put in my variable all the column names of a table so I tried this :

DECLARE my_array ARRAY <STRING>;

SET my_array = (
  SELECT column_name
  FROM my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'my_table'
);

SELECT my_array

I think I have a syntax problem because the error is :

Query error: Cannot coerce expression (
  SELECT column_name
  FROM my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'my_table'
) to type ARRAY<STRING> at [3:16]

Thanks,

Answer 1

Would you try this one ?

DECLARE my_array ARRAY <STRING>;

SET my_array = ARRAY(
  SELECT column_name
  FROM my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'my_table'
);

SELECT my_array;

Or, this will work also.

SET my_array = (
  SELECT ARRAY_AGG(column_name)
  FROM my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'my_table'
);