BigQuery correlated subqueries – transform array to array

I’m trying to join array elements in BigQuery but I am getting the following error message:

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

In my first table I have something like:

field1 | field2 | some_list

Elements in some_list have ids and other data and I’d like to enrich each element from some_list with some fields from a different table (they may exist or not).
I’ve tried to unnest some_list and left join with a different table on id but it seems it’s not allowed.

Any ideas how I can do it? Thanks!

First table:

day             city       orders.id    orders.address
14-06-2021      London      1            abc
                            2            def
                            3            ghi
14-06-2021      Bristol     4            sfd
                            5            sds

Second table:

order.id    order.weight
1            10
2            12
3            35
5            31

Expected result:

day            city     orders.id    orders.address  orders.weight
14-06-2021     London    1           abc            10
                         2           def            12
                         3           ghi            35
14-06-2021     Bristol   4           sfd            NULL
                         5           sds            31

Answer

Consider below generic solution

select any_value(t1).* replace(
    array_agg((select as struct t.*, `order`.weight)) as orders
  )
from first_table t1, t1.orders t
left join second_table t2
on id = `order`.id  
group by to_json_string(t1)    

if applied to sample data in your question – output is