BigQuery中使用CASE WHEN处理同一列中的不同数据类型

问题核心是BigQuery的CASE WHEN会预先计算所有分支的表达式，哪怕条件不满足。所以当遇到非整数字符串时，直接CAST成INT64的操作会触发报错，而非跳过该分支。

以下是两种可行的解决方法：

方案1：用SAFE_CAST实现安全判断+转换 #

SAFE_CAST在转换失败时返回NULL，不会报错，这样CASE WHEN就能正常走分支判断：

SELECT
  CASE 
    WHEN SAFE_CAST(JSON_EXTRACT_SCALAR(payload, '$.job.created_at') AS INT64) IS NOT NULL 
    THEN TIMESTAMP_SECONDS(SAFE_CAST(JSON_EXTRACT_SCALAR(payload, '$.job.created_at') AS INT64))
    ELSE TIMESTAMP(JSON_EXTRACT_SCALAR(payload, '$.job.created_at')) -- 直接解析标准时间戳字符串
  END AS job_created_timestamp 
FROM tableA;

方案2：用SAFE.TIMESTAMP_SECONDS+COALESCE简化逻辑 #

利用SAFE.TIMESTAMP_SECONDS在转换失败时返回NULL的特性，搭配COALESCE依次尝试转换，直到找到有效方式：

SELECT
  COALESCE(
    SAFE.TIMESTAMP_SECONDS(SAFE_CAST(JSON_EXTRACT_SCALAR(payload, '$.job.created_at') AS INT64)),
    TIMESTAMP(JSON_EXTRACT_SCALAR(payload, '$.job.created_at'))
  ) AS job_created_timestamp
FROM tableA;

可选：添加默认值 #

如果需要给既不是整数、也不是标准时间戳格式的字段设置默认值，直接在COALESCE末尾追加即可：

SELECT
  COALESCE(
    SAFE.TIMESTAMP_SECONDS(SAFE_CAST(JSON_EXTRACT_SCALAR(payload, '$.job.created_at') AS INT64)),
    TIMESTAMP(JSON_EXTRACT_SCALAR(payload, '$.job.created_at')),
    TIMESTAMP('1970-01-01 00:00:00') -- 自定义默认时间
  ) AS job_created_timestamp
FROM tableA;

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