BigQuery correlated subqueries - transform array to array

Question

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 1

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

Answer 2

Try this one:

with first_table as (
  select '14-06-2021' as day, 'London' as city, [struct(1 as id, 'abc' as address), (2, 'def'), (3, 'ghi')] as orders union all
  select '14-06-2021' as day, 'Bristol' as city, [struct(4 as id, 'sfd' as address), (5, 'sds')] as orders
),
second_table as (
  select struct(1 as id, 10 as weight) as `order` union all
  select struct(2 as id, 12 as weight) as `order` union all
  select struct(3 as id, 35 as weight) as `order` union all
  select struct(5 as id, 31 as weight) as `order`
  
)
select day, city, array_agg(struct(o.id as id, o.address as address, s.order.weight as weight)) as orders
from first_table, unnest(orders) as o left join second_table as s on o.id = s.order.id
group by day, city

Answer 3

This should work just fine and it seem to do what you are describing:

select t.*,
       (select array_agg(coalesce(ot.other_value, el))
        from unnest(t.some_list) el left join
             othertable ot
             on ot.key = el.key
       ) as new_list
from t;