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,

Jaytiger · Accepted Answer · 2022-05-22 12:25:29Z

7

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

answered May 22, 2022 at 12:25

Jaytiger

12.3k2 gold badges7 silver badges16 bronze badges

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

Comments

Gianfranco Reppucci · Accepted Answer · 2022-05-22 12:35:37Z

2

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

answered May 22, 2022 at 12:35

Gianfranco Reppucci

3043 silver badges6 bronze badges

1 Comment

aloux Over a year ago

It works. Thanks also for the documentation.

Jiho Choi · Accepted Answer · 2022-05-23 02:51:50Z

1

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
;

answered May 23, 2022 at 2:51

Jiho Choi

1,3473 gold badges15 silver badges34 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

# 1) Array

# 2) Table name with column names (Unnested)

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

# 1) Array

# 2) Table name with column names (Unnested)

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related