执行带WHERE条件的SELECT语句报错：无法将FLOAT64字段当作STRING读取

问题根源 #

你用通配符查询的TABLE_2*系列表中，部分分区表的jsonPayload.node.property字段实际存储类型是FLOAT64，和你查看的主表结构不一致。BigQuery处理通配符表时会自动推断字段类型，一旦不同分区间字段类型冲突，就会抛出类型不兼容的错误。

修复方案 #

1. 强制字段类型转换 #

在查询里把该字段显式转为STRING类型，绕过类型推断冲突：

SELECT * EXCEPT(jsonPayload.node.property),
       CAST(jsonPayload.node.property AS STRING) AS jsonPayload_node_property
FROM `TABLE_2*`
WHERE _TABLE_SUFFIX BETWEEN '0240303' AND '0240304'
AND jsonPayload.property1 LIKE '%text-text%'
AND jsonPayload.property2 LIKE '%/text'

如果想保留原字段结构，用SAFE_CAST避免转换失败导致整条数据被过滤：

SELECT * REPLACE(SAFE_CAST(jsonPayload.node.property AS STRING) AS jsonPayload.node.property)
FROM `TABLE_2*`
WHERE _TABLE_SUFFIX BETWEEN '0240303' AND '0240304'
AND jsonPayload.property1 LIKE '%text-text%'
AND jsonPayload.property2 LIKE '%/text'

2. 排查并统一分区表字段类型 #

• 执行下面的SQL找出字段类型不一致的分区：

SELECT
  _TABLE_SUFFIX AS table_suffix,
  TYPEOF(jsonPayload.node.property) AS field_type
FROM `TABLE_2*`
WHERE _TABLE_SUFFIX BETWEEN '0240303' AND '0240304'
GROUP BY table_suffix, field_type

• 对类型为FLOAT64的分区，修改表结构统一为STRING，或者重新写入数据时确保字段类型一致。

3. 排除异常分区 #

如果确定只有个别分区类型异常，直接在查询里排除它们：

SELECT *
FROM `TABLE_2*`
WHERE _TABLE_SUFFIX BETWEEN '0240303' AND '0240304'
AND _TABLE_SUFFIX NOT IN ('异常分区后缀') -- 替换为实际异常分区的后缀
AND jsonPayload.property1 LIKE '%text-text%'
AND jsonPayload.property2 LIKE '%/text'

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