无法基于JSON类型字段聚合创建Google BigQuery物化视图

问题根源 #

BigQuery物化视图的增量更新机制对JSON类型字段的动态解析+聚合场景存在兼容性限制：

• 物化视图需要稳定追踪底层数据的变更，且依赖明确的字段类型来维护增量计算逻辑
• 直接从JSON类型列中UNNEST数组并聚合时，解析出的临时字段类型属于动态推断类型，BigQuery无法为其建立稳定的增量更新规则，导致创建物化视图失败
• 而STRING类型是静态结构，解析后字段类型可被明确识别；非JSON衍生字段本身类型固定，所以聚合操作能正常执行

可行解决方案 #

方案1：在物化视图内将JSON列转为STRING后再解析 #

直接在创建物化视图的SQL中，先把JSON类型的data列转为STRING，再进行数组解析和聚合操作，示例：

CREATE MATERIALIZED VIEW your_view_name
AS
SELECT
  -- 替换为你的分组字段
  id,
  ARRAY_AGG(tags_unnest.name IGNORE NULLS) AS tags
FROM
  `raw_changelog`,
  UNNEST(JSON_EXTRACT_ARRAY(CAST(data AS STRING), '$.tags')) AS tags_json
  , UNNEST([STRUCT(JSON_VALUE(tags_json, '$.name') AS name)]) AS tags_unnest
GROUP BY
  id

方案2：先创建预处理视图固定解析后字段类型 #

先建立一个普通视图，把JSON列解析为明确类型的字段，再基于这个视图创建物化视图：

-- 先创建预处理视图，固定解析后字段类型
CREATE VIEW raw_changelog_parsed AS
SELECT
  -- 保留原有字段
  id,
  -- 解析tags数组并明确类型
  ARRAY(
    SELECT JSON_VALUE(tag, '$.name') 
    FROM UNNEST(JSON_EXTRACT_ARRAY(data, '$.tags')) AS tag
  ) AS tags_list
FROM `raw_changelog`

-- 基于预处理视图创建物化视图
CREATE MATERIALIZED VIEW your_view_name
AS
SELECT
  id,
  ARRAY_AGG(tag_name IGNORE NULLS) AS tags
FROM raw_changelog_parsed,
  UNNEST(tags_list) AS tag_name
GROUP BY
  id

方案3：使用类型明确的JSON函数强制指定字段类型 #

在解析JSON时，用SAFE_CAST或明确的类型转换函数，让BigQuery识别出固定的字段类型，示例：

CREATE MATERIALIZED VIEW your_view_name
AS
SELECT
  id,
  ARRAY_AGG(SAFE_CAST(JSON_VALUE(tags_json, '$.name') AS STRING) IGNORE NULLS) AS tags
FROM
  `raw_changelog`,
  UNNEST(JSON_EXTRACT_ARRAY(data, '$.tags')) AS tags_json
GROUP BY
  id

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