UNNEST on GENERATE_TIMESTAMP_ARRAY() causes syntax error

Question

My goal is to use an existing table with patient_id's and corresponding dates (secifically timestamps) of entry and exit at the hospital (called min_time and max_time) to create a new table where each 4-hour bloc is a row.

Example

My current SQL code is as follows:

CREATE table new_table as(
    SELECT patient_id, 
    UNNEST(GENERATE_TIMESTAMP_ARRAY(min_time, max_time, INTERVAL 4 HOUR)) as time
    FROM parent_table
    ORDER BY patient_id, time)

However I get the following error: 'Syntax error: Expected ")" but got keyword UNNEST'. I can easily create the timestamp_array so I know GENERATE_TIMESTAMP_ARRAY(min_time, max_time, INTERVAL 4 HOUR) works. I believe the issue arises because the format UNNEST(GENERATE...) is not accepted, but am unsure of a better alternative. Any idea how to fix it? (Code run on BigQuery)

Cheers

Answer 1

So it really depends if you want an array in your final output or a "flattened" table.

For an array in your final output...

SELECT 
  patient_id, 
  GENERATE_TIMESTAMP_ARRAY(min_time, max_time, INTERVAL 4 HOUR) as time
FROM parent_table
ORDER BY patient_id

For a flattened version (which sounds like what you are shooting for)...

SELECT 
  patient_id, 
  time
FROM parent_table,
     UNNEST(GENERATE_TIMESTAMP_ARRAY(min_time, max_time, INTERVAL 4 HOUR)) as time
ORDER BY patient_id, time