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

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

Answer 2

It is possible to use string array as #1, but you can also use approach #2 to simply return a table with table_name and column_name.

# 1) Array

DECLARE colum_name_array ARRAY <STRING>;

SET colum_name_array = (
  SELECT ARRAY_AGG(column_name)
  FROM `[project_id].[datset_name].INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = '[table_name]'
);

SELECT colum_name_array;

# 2) Table name with column names (Unnested)

WITH
table_columns AS (
  SELECT table_name, ARRAY_AGG(column_name) as column_name_array
  FROM `[project_id].[datset_name].INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = '[table_name]'
)
SELECT table_name, colum_name
FROM table_columns, UNNEST(column_name_array) as colum_name
;