BigQuery表数据迁移报错：嵌套Float64转Int64类型不匹配

错误原因分析 #

• 字段名拼写错误：你第二次尝试的查询中，在CAST结构体定义时将createdat_date误写为cratedat_date，导致结构体字段与目标表不匹配，触发类型转换异常。
• 旧表存在混合类型元素：旧表的paymentschemavariablesrecords数组中可能包含非STRUCT类型（比如STRING）的元素，UNNEST时BigQuery会将整个数组隐式转为STRING类型，与目标表的ARRAY<STRUCT>类型冲突。
• 隐式类型转换问题：未对嵌套层级的所有可能无效值做安全处理，导致部分元素无法正确解析为STRUCT。

修正后的查询语句 #

INSERT INTO `MongoDB.MS-DB_MS-COLLECTION_temp`
SELECT
  t.* EXCEPT(paymentschemavariablesrecords),
  -- 只保留能解析为STRUCT的元素，过滤无效值
  ARRAY(
    SELECT AS STRUCT
      createdat_date,
      creatorid,
      creatorname,
      creatorroles,
      creatorsurname,
      ARRAY(
          SELECT AS STRUCT
              code,
              id_oid,
              kind,
              name,
              -- 安全转换Float64到Int64，处理空值或非数字情况
              SAFE_CAST(SAFE_ROUND(value) AS INT64) AS value
          FROM UNNEST(newvalue)
      ) AS newvalue,
      ARRAY(
          SELECT AS STRUCT
              code,
              id_oid,
              kind,
              name,
              SAFE_CAST(SAFE_ROUND(value) AS INT64) AS value
          FROM UNNEST(oldvalue)
      ) AS oldvalue
    FROM UNNEST(t.paymentschemavariablesrecords)
    -- 过滤掉非STRUCT类型的无效元素
    WHERE t.paymentschemavariablesrecords IS NOT NULL AND JSON_TYPE(t.paymentschemavariablesrecords) = 'ARRAY'
  ) AS paymentschemavariablesrecords
FROM `MongoDB.MS-DB_MS-COLLECTION` t;

关键优化点 #

• 安全转换函数：使用SAFE_ROUND和SAFE_CAST替代普通的ROUND和CAST，避免因空值、非数字值导致的查询失败。
• 过滤无效元素：通过JSON_TYPE检查数组类型，确保只处理有效的STRUCT数组元素，避免混合类型导致的隐式转换。
• 字段名一致性：确保结构体字段名与目标表完全一致，避免字段不匹配引发的类型错误。

额外验证步骤 #

如果上述查询仍有问题，可以先运行以下语句检查旧表中paymentschemavariablesrecords字段的类型分布，针对性处理：

SELECT
  JSON_TYPE(paymentschemavariablesrecords) AS element_type,
  COUNT(*) AS count
FROM `MongoDB.MS-DB_MS-COLLECTION`
GROUP BY element_type;

内容的提问来源于stack exchange，提问作者Matías