BigQuery Error: Unable to split into re-field

Question

I have two tables table1 (complex with repeating / writing columns) and table2 (pretty simple). I am trying to create a new table with all the 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 receive an error message Unable to split into repeated field , as shown in the image below. Diagrams of the two tables are also shown in the corresponding images.

Is there a general rule when you need to combine data from two tables? Am I generally trying to do this?

Actual tables are much more complicated. I show enough context that reproduces the problem.

Answer 1

Before joining them, you will need FLATTEN () your tables.

This does not 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 

It 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 that you posted while editing, the work request:

 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 managed to FLATTEN the data (I had to apply it twice), but not to restore the original structure - combining one of the tweaks is more difficult.

I see what you want to do is enrich some substrings?