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?
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)