CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
user12632521
user12632521

Reputation: 97

Create array with column names in BigQuery

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?

Upvotes: 1

Views: 1937

Answers (1)

Cylldby
Cylldby

Reputation: 1978

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.

Upvotes: 2

Related Questions