How to Group By all fields nested tables in a Left Join query in BigQuery?

Question

I have about 10 tables that I make one big nested tables by rounds with the following query:

   R1 AS(
      SELECT ANY_VALUE(Table1).*, ARRAY_AGG(( SELECT AS STRUCT Table2.* EXCEPT(ID))) AS Table2
      FROM Table1 LEFT JOIN Table2 USING(ID)
      GROUP BY Table1.ID),

   R2 AS(
      SELECT ANY_VALUE(R1).*, ARRAY_AGG(( SELECT AS STRUCT Table3.* EXCEPT(ID))) AS Table3
      FROM R1 LEFT JOIN Table3 USING(ID)
      GROUP BY R1.ID),
...

  
   SELECT ANY_VALUE(R9).*, ARRAY_AGG(( SELECT AS STRUCT Table10.* EXCEPT(ID))) AS Table10
   FROM R9 LEFT JOIN Table10 USING(ID)

The thing is that for example in my first table I can have two records with the same ID but some other fields will be different and I want to consider them as two distinct records and thus group by all the fields of the table while I join. Then I want to do the same with all the "sub-table" (the R tables in the query), so I will able to group by all the fields of the nested tables.

How can I do it easily ?

I tried GROUP BY Table1.* but it doesn't work...

Thank you in advance

Answer 1

Try to_json_string:

...
FROM Table1 t1
...
GROUP BY to_json_string(t1)