STRUCT in Big Query From another table

Question

i have some problem in BigQuery,

i have 2 tables first tables is paymentSchedule

ID	index_month	amount
aaa	1	1000000
aaa	2	2000000
bbb	1	500000
bbb	2	2500000
bbb	3	2000000
ccc	1	2000000

second table is BorrowerTable

ID	Name
aaa	Alexa
bbb	Jorde
ccc	Juan

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

ID	Name	index_month	amount
aaa	Alexa	1	1000000
		2	2000000
bbb	Jorde	1	500000
		2	2500000
		3	2000000
ccc	Juan	1	2000000

i already write query but it doesnt work

select *, STRUCT[select * from paymentSchedule where id=BorrowerTable.ID]from BorrowerTable

Sergey Geron Sergey Geron · Accepted Answer · 2021-03-23T07:18:09

with paymentSchedule as (
  select 'aaa' as id, 1 as index_month, 1000000 as amount union all
  select 'aaa', 2, 2000000 union all  
  select 'bbb', 1, 500000 union all  
  select 'bbb', 2, 25000000 union all    
  select 'bbb', 3, 20000000 union all    
  select 'ccc', 1, 20000000
),
BorrowerTable as (
  select 'aaa' as id, 'Alexa' as name union all
  select 'bbb', 'Jorde' union all
  select 'ccc', 'Juan'
)
select *, array(select struct(index_month, amount) from paymentSchedule where id=BorrowerTable.ID)
from BorrowerTable

STRUCT in Big Query From another table

1 Answers