Create nested table from header and details table

Question

I would like to create a nested table with order data. I have orders and order_details tables. The key is order_id. When I explicit write the field names it works fine but my tables have like 20 fields each. Is there a way to select all fields?

This what I do now (example with a few fields):

with orders as(
    select
        order_id
        ,order_date
        ,store
    from `orders`
)
, order_details as(
    select 
        order_id
        ,order_date
        ,product_id
        ,amount
    from `order_details`
)
select 
   orders.order_id
   ,orders.order_date
   ,orders.store
   ,array_agg(struct(order_details.product_id,order_details.amount)) as order_details
from orders
   left join order_details using(order_id)
group by 1,2,3

I would like to do something like this:

with orders as(
    select
        *
    from `orders`
)
, order_details as(
    select 
        *
    from `order_details`
)
select 
   orders.*
   ,array_agg(struct(select order_details.*) as order_details
from orders
   left join order_details using(order_id)
group by 1,2,3,4,5.........................

Any suggestions how to do this? The group-by will have to include all fields from the orders table. Is there a way to this in a better way?

Answer 1

Consider below approach - does exactly what you ask

select 
  any_value(o).*,
  array_agg(d) as order_details
from orders o
left join order_details d
using(order_id)
group by to_json_string(o)