Analytics Engineering, Bigquery, Sql, Data Engineering, Editors Pick.
FROM UNNEST(promotion_history) UNION ALL SELECT AS STRUCT -1 as promotion_value, end_datetime, FROM UNNEST(promotion_history) WHERE end_datetime < next_event_datetime OR next_event_datetime IS NULL ) as events ... The second part of the union is what reintroduces the previously missing