CODEWITHSUNDEEP
structgoogle-bigquery
rambo vernando
rambo vernando

Reputation: 49

using STRUCT in bigquery

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 ?

Upvotes: 0

Views: 554

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10152

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

Upvotes: 1

Related Questions