Save BigQuery results to array

Question

I have a query that looks like this

SELECT ids FROM `table_name`

The results set is as follows

| ids |
|-----|
| 1   |
| 2   |
| 3   |

I need to save this result set to an ARRAY so that I can later loop over it. Defining the array manually is not an option since my result set will get larger over time adding 4, 5, 6, etc. That's why I can't just use:

SELECT GENERATE_ARRAY(1, 2, 3) AS my_array

I tried

SELECT GENERATE_ARRAY(SELECT ids FROM `table_name`) AS my_array

but it doesn't work.

I'm basically looking for the inverse of UNNEST.

Answer 1

Below is for BigQuery Standard SQL - usually ARRAY_AGG can be used as inverse of UNNEST

#standardSQL
SELECT ARRAY_AGG(ids) ids
FROM `project.dataset.table`

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 ids UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 
)
SELECT ARRAY_AGG(ids) ids
FROM `project.dataset.table`

with result

Row ids  
1   1    
    2    
    3