How to split the GENERATE_DATE_ARRAY in a date for a single row in Google Bigquery to create a date view?

Question

I'm attempting to create a date view using the MIN and MAX of dates from another table in Google Bigquery. I've come across GENERATE_DATE_ARRAY and have been able to create the dates I'm after in a single array. However, splitting them out to a single date per row has been the hard part.

I've attempted to use UNNEST to do this but when I run the code it only gives me the first result so I'm missing something here.

This is the code I've used to generate the array, but I'm stuck on getting it to split out:

SELECT GENERATE_DATE_ARRAY( 
              MIN(CAST(journaldate AS DATE)), 
              MAX(CAST(journaldate AS DATE))
            )
          AS Period
FROM
dataset.table

Answer 1

I think you want:

SELECT Period
FROM (SELECT MIN(CAST(journaldate AS DATE)) as min_date,
             MAX(CAST(journaldate AS DATE)) as max_date
      FROM dataset.table
     ) t JOIN
     UNNEST(GENERATE_DATE_ARRAY(t.min_date, t.max_date)) period