How to rearrange/reorder nested repeated column in Bigquery when UNION tables

0
votes

I have two Bigquery tables with similar schemas, but different order

enter image description here

When union those 2 tables using query below,

select id, timestamp, products, count_total_visit, count_unique_session from table1
union all
select id, timestamp, products, count_total_visit, count_unique_session from table2

This is the error I got

Column 3 in UNION ALL has incompatible types: ARRAY<STRUCT<name STRING, group STRING, ...>>, ARRAY<STRUCT<name STRING, group STRING, ...>> at [...]

How to re-arrange the order of schema with nested repeated fields so that 2 tables can be union by keeping the schema format as same as the source table?

1
google-bigquery

1 Answers

1
votes

Below will do the trick

select id, timestamp, products, count_total_visit, count_unique_session from table1
union all
select * replace (array( 
    select as struct * replace (array( 
        select as struct paiment_method, count_total_trx, sum_total_gbv, sum_total_revenue
          from product.details
        ) as details
      )
    from t.products product
  ) as products)
from table2 t