Looker Studio无法读取BigQuery未知键JSON解析结果的替代方案咨询

方案1：原生JSON函数+UNNEST展开（适用于顶级键值对） #

纯用BigQuery内置函数实现，完全适配Looker Studio的数据源要求，无需自定义函数或复杂正则：

WITH raw_data AS (
  SELECT 
    date_column,
    json_column
  FROM your_dataset.your_table
)
SELECT
  date_column,
  key,
  -- 若值为数字/布尔，可替换为CAST(JSON_EXTRACT(json_column, CONCAT('$.', key)) AS INT64)等类型转换
  JSON_EXTRACT_SCALAR(json_column, CONCAT('$.', key)) AS value
FROM raw_data,
UNNEST(JSON_KEYS(json_column)) AS key

核心逻辑 #

• JSON_KEYS(json_column)提取JSON中所有顶级键，返回数组格式
• UNNEST将键数组拆分为单独行，实现一行JSON对应多行键值对
• CONCAT('$.', key)动态构造JSON路径，适配任意未知键名

方案2：递归展开嵌套JSON（处理多层级结构） #

如果JSON存在嵌套对象，可通过递归CTE逐层展开所有层级的键值对：

WITH RECURSIVE json_flattener AS (
  SELECT
    date_column,
    json_column,
    CAST(NULL AS STRING) AS parent_key,
    JSON_KEYS(json_column) AS keys
  FROM your_dataset.your_table
  UNION ALL
  SELECT
    date_column,
    JSON_EXTRACT(json_column, CONCAT('$.', keys[OFFSET(0)])) AS json_column,
    CONCAT(IFNULL(parent_key || '.', ''), keys[OFFSET(0)]) AS parent_key,
    -- 判断当前节点是否为对象，是则继续提取键，否则终止递归
    CASE WHEN JSON_TYPE(JSON_EXTRACT(json_column, CONCAT('$.', keys[OFFSET(0)]))) = 'OBJECT' 
         THEN JSON_KEYS(JSON_EXTRACT(json_column, CONCAT('$.', keys[OFFSET(0)])))
         ELSE [] END AS keys
  FROM json_flattener
  WHERE ARRAY_LENGTH(keys) > 0
)
SELECT
  date_column,
  parent_key AS key,
  -- 统一处理字符串/数字/布尔类型的值输出
  CASE WHEN JSON_TYPE(json_column) = 'STRING' 
       THEN JSON_EXTRACT_SCALAR(json_column, '$')
       ELSE CAST(json_column AS STRING) END AS value
FROM json_flattener
WHERE ARRAY_LENGTH(keys) = 0
AND parent_key IS NOT NULL

注意点 #

• 两个方案均依赖BigQuery原生函数，无临时JS函数或RE2不支持的正则语法，完全兼容Looker Studio数据拉取规则
• 若JSON包含数组类型，可在UNNEST环节增加数组展开逻辑，结合OFFSET提取数组元素后再解析键值对

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