BQ中合并两个结构体数组去重：解决地址重复显示问题

问题描述 #

需要在BigQuery中构建如下嵌套数据结构：

id
addresess<struct>
    id
    street
    number
    city
contracts<struct>
    other_id 
    agency_code
    latitude
    longitude
    payments<struct>
            id_payment
            payment
            date_of_payment

已分别创建地址表和合同表：

地址表创建语句 #

create table address_table as 
select id,
    array_agg(struct(
                    a.street as street,
                    a.number as number,
                    a.city as city 
    )) as addresess
    from a_address_table
    group by id

合同表创建语句 #

create table contracts_table as 
select id,
    array_agg(struct(
                    a.other_id as other_id,
                    a.agency_code as agency_code,
                    p.payments              --ommited the payments struct
    )) as contracts
    from a_contracts_table a left join payments_temp p on a.other_id=p.other_id
    group by id

以id=5的用户为例，该用户有1个地址和3个不同other_id的合同，尝试用以下语句创建最终表：

create table final_table as 
    select b.id, 
    array_agg(ad) as addresess,
    array_agg(co) as contracts
    from address_table a cross join unnest(a.addresess) ad with offset d1 
    left outer join contracts_table b on a.id=b.id, unnest(b.contracts) co with offset s1
    where d1=s1 
    group by id

问题：该用户的地址因关联3个合同而重复显示3次，当前结果中地址每行都重复，期望地址仅显示一次。

解决方法 #

问题出在不必要地拆分数组并通过偏移量关联，这会让每个地址和每个合同行强制匹配，导致地址被重复聚合。

正确的做法是直接按id关联两个已聚合好的表，保留原有的数组结构：

create table final_table as 
select 
    coalesce(a.id, b.id) as id,
    a.addresess,
    b.contracts
from address_table a
full outer join contracts_table b 
on a.id = b.id;

逻辑说明 #

1. 两个表已经按id完成了数组聚合，直接通过id关联即可，不需要拆分数组再重新聚合。
2. 使用full outer join确保即使某用户只有地址或只有合同，数据也能被保留；如果确认所有用户同时存在地址和合同，改用inner join即可。
3. 最终每个id对应一行数据，addresess数组包含该用户的所有地址，contracts数组包含所有合同及嵌套的支付信息，完全符合需求，不会出现地址重复问题。

验证id=5的结果可执行：

select * from final_table where id=5;

内容的提问来源于stack exchange，提问作者Naty Bizz