STRUCT字段使用UNION ALL时存在冲突问题

Let's break down why you're seeing this behavior in BigQuery.

First, your original query runs smoothly because the struct definitions match exactly in field order, data types, and names across both SELECT statements in the UNION ALL:

SELECT STRUCT<price STRUCT<previous FLOAT64, `current` FLOAT64, currency STRING>> (STRUCT(120.0 AS previous, 110.0 AS `current`, "BR")) 
UNION ALL 
SELECT STRUCT<price STRUCT<previous FLOAT64, `current` FLOAT64, currency STRING>> (STRUCT(121.0 AS previous, 111.0, "BR"))

Now, if you swap the order of currency and current in the struct definition (and corresponding values), you'll hit a schema conflict. Here's the key reason:

• BigQuery treats structs as ordered collections of fields, not just named containers. Even if all field names and data types are present, a mismatch in their sequence means the structs are considered distinct data types.
• When using UNION ALL, all result sets must share an identical schema—this includes the exact order of fields within every nested struct.

For example, this modified query will fail:

-- This will throw a schema conflict error
SELECT STRUCT<price STRUCT<previous FLOAT64, currency STRING, `current` FLOAT64>> (STRUCT(120.0 AS previous, "BR", 110.0 AS `current`)) 
UNION ALL 
SELECT STRUCT<price STRUCT<previous FLOAT64, `current` FLOAT64, currency STRING>> (STRUCT(121.0 AS previous, 111.0, "BR"))

BigQuery rejects it because the price struct in the first SELECT has a different field order than the second one—even though all fields exist with correct data types.

To resolve this, always ensure every struct in your UNION ALL has identical field order, names, and data types at every nested level. Align your struct definitions across all parts of the union to avoid schema mismatches.

内容的提问来源于stack exchange，提问作者Willian Fuks