using STRUCT in bigquery

Question

i have some problem in BigQuery,

i have 2 tables first tables is paymentdate

Customer_id	paid_date
aaa	2021/01/23
aaa	2021/01/24
bbb	2020/01/12

second table is amount_table

Costemer_id	date	conceptID	amount
aaa	2021/01/23	principal	1000
aaa	2021/01/23	interest	200
aaa	2021/01/24	late_fee	30
aaa	2021/01/24	principal	1000
bbb	2020/01/12	principal	250

and i want to write query using STRUCT and the result that i want is :

customer_id	paid_date	concept_id	amount
aaa	2021/01/23	principal	100000
		interest	200
aaa	2021/01/24	late_fee	30
		principal	10000
bbb	2020/01/12	principal	250

i already write query but it doesnt work

select paymentdate.customer_id, paymentdate.paid_date , array(select struct(concept_id,amount) from amount_table where amount_table.customer_id=paymentdate.customer_id and amount_table.paid_date=paymentdate.paid_date) from paymentdate

anyone can help ?

Answer 1

Try replacing paid_date with date in your query: and amount_table.date=paymentdate.paid_date.

Also consider array_agg:

select 
  paymentdate.customer_id,
  paymentdate.paid_date,
  array_agg(struct(concept_id,amount))
from paymentdate join amount_table 
  on paymentdate.customer_id = amount_table.customer_id
  and paymentdate.paid_date = amount_table.date
group by 1, 2