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?

Cylldby · Accepted Answer · 2022-03-20 14:07:46Z

2

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.

answered Mar 20, 2022 at 14:07

Cylldby

1,9881 gold badge6 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

Create array with column names in BigQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related