作者:sjf66355555 | 来源:互联网 | 2023-10-12 09:34
我有两个Google BigQuery表,它们的数据结构相同,但字段顺序不同:
+- table1: record
| +- FIELD: record (repeated)
| | |- FIELD.blue: string
| | |- FIELD.yellow: integer
+- table2: record
| +- FIELD: record (repeated)
| | |- FIELD.yellow: integer
| | |- FIELD.blue: string
我需要连接两个表:
select * from `table1`
union all
select * from `table2`
但出现以下错误:
Column 5 in UNION ALL has incompatible types
有没有一种方法可以对嵌套字段进行重新排序,以便我可以合并两个表,或者还有另一种方法可以执行此操作?
这是我能做的最好的事情-在查询时使用REPLACE
在一个表中重新生成嵌套对象:
WITH ta AS (SELECT 'x' id,[STRUCT('a' AS a,3 AS b)] st),tb AS (SELECT 'y' id,[STRUCT(1 AS b,'b' AS a)] st)
SELECT *
FROM ta
UNION ALL
SELECT * REPLACE ((SELECT ARRAY_AGG(STRUCT(a,b)) FROM UNNEST(st)) AS st)
FROM tb