如何将String array转换为Int64 实现不同类型字段两表关联

直接将ARRAY类型强转为INT64类型报Invalid cast from ARRAY to INT64是必然结果：ARRAY是多值集合类型，INT64是单值数值类型，二者数据维度完全不同，不存在直接强转的逻辑。要实现两表关联，必须先将数组拆解为单值，再做字段类型对齐。

步骤1：拆解表A的数组字段+类型对齐 #

先把表A中String array类型的ID字段拆成多行单值字符串，再将每个单值字符串转换为和表B一致的INT64（即BIGINT）类型，生成可用于关联的匹配字段。
不同SQL引擎的数组拆解语法略有区别：

• 适配BigQuery、Presto、Trino等引擎（支持UNNEST）的处理逻辑：

SELECT
  a_raw.*,
  -- 单值字符串转INT64，建议用安全转换函数避免非数字值报错
  SAFE_CAST(single_id AS INT64) AS match_id
FROM tableA a_raw,
UNNEST(a_raw.string_array_id) AS single_id
-- 过滤转换失败的无效ID
WHERE SAFE_CAST(single_id AS INT64) IS NOT NULL

• 适配Hive、Spark SQL等引擎（支持explode）的处理逻辑：

SELECT
  a_raw.*,
  TRY_CAST(single_id AS BIGINT) AS match_id
FROM tableA a_raw
LATERAL VIEW explode(a_raw.string_array_id) tmp AS single_id
WHERE TRY_CAST(single_id AS BIGINT) IS NOT NULL

步骤2：执行关联查询 #

用处理好的表A中间结果和表B，基于类型完全一致的match_id和表B的ID字段做关联即可：

SELECT
  a.*,
  b.*
FROM (
  -- 此处替换为你所用引擎对应的表A预处理逻辑
  SELECT
    a_raw.*,
    SAFE_CAST(single_id AS INT64) AS match_id
  FROM tableA a_raw,
  UNNEST(a_raw.string_array_id) AS single_id
  WHERE SAFE_CAST(single_id AS INT64) IS NOT NULL
) a
INNER JOIN tableB b
ON a.match_id = b.id

• 如果表A的单条记录存在数组内多个ID都能匹配表B的情况，上述写法会返回多条重复的表A记录，可根据业务需求对关联结果做去重，或者加聚合逻辑保留需要的字段
• 必须提前处理数组内的非数字字符串内容，否则转换时会抛出类型转换错误，优先使用SAFE_CAST/TRY_CAST这类安全转换函数，不要直接用普通CAST
• 不要尝试将整个数组转成单值INT64，任何SQL引擎都不支持集合类型到单值标量类型的直接强转，必须先做数组展开

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