SQL Pro tips : GCP BigQuery SQL CROSS JOIN with UNPIVOT UNNEST

Introduction

TL;DR : Why to use UNPIVOT over UNION ALL ? Performance, UNION ALL scans all the data for each subquery, with UNPIVOT the scan happens only once.
We shall perform now the same operation with BigQuery
Feel free to check the Oracle XE CROSS JOIN UNPIVOT tips or a post on the internet.
I'm writing this guide to provide a simple step-by-step manual easy to understand.

UNPIVOT

Let's start straight away with something trivial :

select 'SUNDAY' as day0, 'MONDAY' as day1;

Similar example with array :

select array['SUNDAY','MONDAY'] as day;

Look carefully, in fact there is only 1 row :

And once you have performed UNNEST :

select * from unnest(array['SUNDAY','MONDAY']);

you have 2 rows :

As explained in the UNNEST documentation
the operator takes an array as an input and returns a table with a single row for each element in the array.

What if we have to UNPIVOT multiple values ?
The comma in between the UNNEST in the query below stands for the CROSS JOIN :

select * from unnest(array['SUNDAY','MONDAY']) as day, unnest(array[0,1]) as day_order;

This is now what we wanted to achieve, hence let's try to use UNPIVOT

with data as (select 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select * from data
unpivot(
  (day, day_order) for day_column in (
    (day0, day_order0) as 'day0',
    (day1, day_order1) as 'day1'
  ) 
);

Great ! Now we know how to use UNNEST and UNPIVOT, let's dive into CROSS JOIN.

CROSS JOIN

In a nutshell CROSS JOIN
operation is simple = it's just a Cartesian product of the two tables, take all the possible combinations from the values on the left with the values on the right.
Few examples :

with 
  week_data as (select '2024W01' as week_ref),
  day_data as (select 'SUNDAY' as day_ref)
select * from week_data cross join day_data;

Now let's have some UNION ALL

with 
  week_data as (select '2024W01' as week_ref),
  day_data as (
    select 'SUNDAY' as day_ref
    union all 
    select 'MONDAY' as day_ref)
select * from week_data cross join day_data;

a bit of array :

with 
  week_data as (select '2024W01' as week_ref),
  day_data as (
    select array['SUNDAY', 'MONDAY'] as day_ref)
select * from week_data cross join day_data;

look carefully, there is only 1 row in the result :

let's have 2 rows with UNNEST :

with 
  week_data as (select '2024W01' as week_ref),
  day_data as (
    select * from unnest(array['SUNDAY', 'MONDAY']) as day_ref)
select * from week_data cross join day_data;

and finally the full power of CROSS JOIN :

with 
  week_data as (select * from unnest(array['2024W01', '2024W02']) as week_ref),
  day_data as (
    select * from unnest(array['SUNDAY', 'MONDAY']) as day_ref)
select * from week_data cross join day_data;

We are ready now to combine the CROSS JOIN with UNPIVOT or UNNEST.

CROSS JOIN UNPIVOT

Let's combine both CROSS JOIN and UNPIVOT, first I will generate some data that we would like to slice and to stack the chunks :

with 
  in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select * from in_data;

And let's have the desired result :

with 
  in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select week_ref, day, day_order from in_data
unpivot(
  (day, day_order) for day_column in (
    (day0, day_order0) as 'day0',
    (day1, day_order1) as 'day1'
  ) 
);

Thanks to the examples above you know that what happens here is CROSS JOIN of the week_ref on the left with the result of UNPIVOT on the right.

If you have only one column you can take a shortcut with UNNEST :

with 
  in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select week_ref, day from in_data
cross join unnest(array[day0, day1]) as day;

Enjoy!