Combine data of 2 tables with same array struct in Bigquery

Question

I need to combine the data of 2 tables, both has the same structure

Table 1:

key   A.a A.b
1      2    2
       3    3
       4    4
2      1    1

table 2 :

key   A.a  A.b
1      5    5
       6    6

How can i have with a simble query this result :

key   A.a A.b
1      2    2
       3    3
       4    4
       5    5
       6    6
2      1    1

without any unnest() functions. I don't want to use unnest() because of the huge volume of these tables which is bery bad for the global performance of this merge.

Thank you very much !

Answer 1

There are two solution without unnest by using join or union and group commands. Two Arrays can be commbinded by the commands array_concat or array_concat_agg.

With tbl1 as (
  Select 1 key, array_agg(struct(a,a as b)) A from unnest([2,3,4]) a
  union all Select 2, [(struct(1 as a,1 as b))] A  
),
tbl2 as (
  Select 1 key, array_agg(struct(a,a as b)) A from unnest([5,6]) a
  union all Select 9, [(struct(19 as a,19 as b))] A  
)

/*
Select key,array_concat_agg(A)
 from (Select * from tbl1 union all select * from tbl2)
 group by 1
*/
Select key, 
#t1.A, t2.A
array_concat(ifnull(t1.A,[]),ifnull(t2.A,[]) )
from tbl1 t1
full join tbl2 t2
using(key)