如何替换BigQuery中JSON字段空值并转换为ARRAY<INT64>？

当JSON数组包含null值时，直接使用JSON_VALUE_ARRAY会报错，因为该函数默认不允许数组中存在null元素。以下两种方法可以解决这个问题：

方法一：JSON_QUERY_ARRAY + SAFE_CAST组合 #

先通过JSON_QUERY_ARRAY提取包含null的JSON数组，再用SAFE_CAST安全转换每个元素为INT64（遇到null时会返回null而非报错），最后重新聚合为数组：

SELECT
  ARRAY(
    SELECT SAFE_CAST(item AS INT64)
    FROM UNNEST(JSON_QUERY_ARRAY(your_json_column, '$.values')) AS item
  ) AS int64_array
FROM your_table

方法二：开启JSON_VALUE_ARRAY的allow_nulls参数 #

JSON_VALUE_ARRAY支持第三个参数allow_nulls，将其设置为TRUE即可允许数组包含null元素，之后再用SAFE_CAST转换类型：

SELECT
  ARRAY(
    SELECT SAFE_CAST(item AS INT64)
    FROM UNNEST(JSON_VALUE_ARRAY(your_json_column, '$.values', TRUE)) AS item
  ) AS int64_array
FROM your_table

两种方法最终都会生成包含null的ARRAY<INT64>结果，比如输入{"values":[1, 2, null, 4, null]}会输出[1,2,null,4,null]。

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