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

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,

CodePudding user response：

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'
);

CodePudding user response：

The output of such a query is not an array as the declared variable, so you have to aggregate results as an array to match the proper type:

DECLARE something ARRAY<STRING>;

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

SELECT something;

You can find the documentation at this page