StructOfArray (SOA), ArrayOfStruct (AOS) in BigQuery

Let's start with the following CTE to work with:

with tbl as (
  select 1 as x, 'a' as y union all
  select 2, 'b'
) SELECT ...

What is the suggested way to get an ARRAY of STRUCTs in BQ? So far I'm thinking:

with tbl as (
  select 1 as x, 'a' as y union all
  select 2, 'b'
)
SELECT ARRAY(SELECT AS STRUCT * FROM tbl)

Not too bad. But then how to do a STRUCT of ARRAYs? This one seems much tougher, and I can only think of 'manually' doing it for each column such as:

with tbl as (
  select 1 as x, 'a' as y union all
  select 2, 'b'
) 
SELECT AS STRUCT 
    ARRAY(SELECT x FROM tbl) x, 
    ARRAY(SELECT y FROM tbl) y

Unfortunately, though, this way requires (1) a sub-select for each column; and (2) renaming each column after. In other words, it doesn't work 'generically' like the first way. Is there a general-purpose way to do the SoA in BQ?

Solution

[EDIT:] This is the shortest way:

with tbl as (
  select 1 as x, 'a' as y union all
  select 2, 'b'
)

SELECT ARRAY(SELECT AS STRUCT * FROM tbl)

Another solution is this:

with tbl as (
  select 1 as x, 'a' as y union all
  select 2, 'b'
),
tbl1 as (
  SELECT struct(
    array_agg(x) as x,
    array_agg(y) as y  
  ) as temp_col
   from tbl
)
SELECT AS STRUCT temp_col from tbl1