Join two tables based on a nested field in bigquery-CodePudding

i want to join two tables "mapping_physical_countries" and "countries" based on 'iso_country_code' which is a nested field with the 'country_alpha3_id' from countries. The idea is to add a new column to the "mapping_physical_countries" that contains the countries label for each element in iso_country_code (it will be also a nested field). My query is not working because of this error :

No matching signature for operator = for argument types: ARRAY<STRING>, STRING. Supported signature: ANY = ANY

This is the "mapping_physical_countries" table :

An extract of "countries" table:

CodePudding user response：

I hope it can help.

I propose you a query to solve your issue :

select 
  mapping_countries.multidivision_cluster,
  country_code as iso_country_code,
  countries.country_alpha3_id
from `project.dataset.mapping_physical_countries`  mapping_countries,
unnest(mapping_countries.iso_country_code) as country_code
inner join `project.dataset.countries` countries on country_code = countries.country_alpha3_id

I used unnest on iso_country_code array.