Create array with column names in BigQuery

Question

I have a table with multiple columns. I want to create an array that contains all names of the columns that appear in the table, such that I can work with this array later.

I selected the column names using this query:

SELECT column_name FROM Books1.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table1'

Now I want to declare and set an array with the column names:

DECLARE column_names ARRAY <STRING>
SET column_names = **

I don't know with what lines of code should I replace **. On the internet I only found examples where I can create an array with a predefined set of values.

Can anyone help?

Answer 1

You should be able to achieve what you want with the following:

DECLARE column_names ARRAY <STRING>
SET column_names = (SELECT arraY_agg(column_name) FROM Books1.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table1'
);

Adding array_agg makes the query return an array of the column names.