How to join an identifier table onto an array field in BigQuery?

Question

Tbl1

id	eez
1	8438
	8493

Tbl2

eez_id	name
8438	Greenlandic Exclusive Economic Zone
8493	Canadian Exclusive Economic Zone

Desired output

id	eez	name
1	8438	Greenlandic Exclusive Economic Zone
	8493	Canadian Exclusive Economic Zone

So far I've done the below, but it is giving me undesired results. Any thoughts here?

select a.*, b.name
from tbl1 as a
left join tbl2 as b
on b.eez_id in unnest(a.eez);

Mikhail Berlyant · Accepted Answer · 2022-01-04 20:38:45Z

1

Consider below approach

select id, 
  array(
    select as struct eez_id as eez, name
    from t1.eez eez_id
    join tbl2 
    using(eez_id)
  ) eezs
from tbl1 t1

if applied to sample data in your question - output is

answered Jan 4, 2022 at 20:38

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1