BigQuery error: Cannot partition on repeated field

Question

I have two tables table1 (complex one with repeated/record columns) and table2 (fairly simple). I am trying to create a new table with all columns from table1 with one column from table2 using the following query:

select t1.id, t1.experience.desc, t1.experience.organization.*, t1.experience.department, t2.field2 as t1.experience.organization.newfield, t1.family_name
from [so_public.table1] as t1 left join each [so_public.table2] as t2
on t1.experience.organization.name = t2.field1

I get an error Cannot partition on repeated field as shown in the image below. The schemas of the two tables are also shown in their respective images.

Is there a general rule of thumb here when one wants to merge data from two tables? Is what I am trying to do at all possible?

The actual tables are much more complex. I am only showing enough context that reproduces the issue.

Answer 1

You will need to FLATTEN() your tables before joining them.

This doesn't work:

SELECT a.fullName, b.fullname
FROM [bigquery-samples:nested.persons_living] a
JOIN [bigquery-samples:nested.persons_living] b
ON a.citiesLived.place=b.citiesLived.place
LIMIT 1000

Error: Cannot join on repeated field citiesLived.place

This does:

SELECT a.fullName, b.fullname
FROM FLATTEN([bigquery-samples:nested.persons_living], citiesLived) a
JOIN FLATTEN([bigquery-samples:nested.persons_living], citiesLived) b
ON a.citiesLived.place=b.citiesLived.place
LIMIT 1000

Answer 2

With the public sample you published on edit, a working query:

select t1.id, t1.experience.desc, t1.experience.department, t1.experience.organization.*, t2.field2 as t1.experience.organization.newfield, t1.family_name
from FLATTEN(FLATTEN([earnest-stock-91916:so_public.table1], experience.organization), experience) as t1 left join each [earnest-stock-91916:so_public.table2] as t2
on t1.experience.organization.name = t2.field1;

I was able to FLATTEN the data (had to apply it twice), but not to recover the original structure - joining against one of the sub-rows is harder.

I see what you want to do is enrich some of the sub-rows?