在BigQuery中处理含动态键的JSON数据查询问题

针对动态日期键的JSON字段，你可以通过JSON_OBJECT_KEYS提取所有顶级键，再用UNNEST展开成行，结合动态拼接JSON路径的方式实现查询，同时过滤掉总计数据。

示例SQL #

WITH parsed_dates AS (
  -- 提取所有动态日期键并展开为行
  SELECT
    json_response,
    date_key AS ref_date
  FROM
    `你的项目名.你的数据集名.你的表名`,
    UNNEST(JSON_OBJECT_KEYS(json_response)) AS date_key
  -- 过滤非日期格式的键（比如总计项），根据实际情况调整规则
  WHERE REGEXP_CONTAINS(date_key, r'^\d{4}-\d{2}-\d{2}$')
)
SELECT
  ref_date,
  -- 提取paesi字段
  JSON_EXTRACT_SCALAR(json_response, CONCAT('$.', ref_date, '.paesi')) AS paesi,
  -- 提取fatturato的数值字段，如需转成数值类型可加SAFE_CAST
  JSON_EXTRACT_SCALAR(json_response, CONCAT('$.', ref_date, '.fatturato.numero')) AS fatturato_numero,
  SAFE_CAST(JSON_EXTRACT(json_response, CONCAT('$.', ref_date, '.fatturato.valore')) AS FLOAT64) AS fatturato_valore
FROM parsed_dates;

关键说明 #

1. JSON_OBJECT_KEYS + UNNEST：把JSON的动态日期键转换成独立的行，解决静态键无法匹配的问题。
2. 动态JSON路径：用CONCAT拼接$.{ref_date}.xxx格式的路径，让JSON_EXTRACT能定位到每个日期下的具体字段。
3. 过滤总计：通过正则表达式筛选日期格式的键，若总计是在日期对象内部的标识字段（比如is_total），可修改WHERE条件为JSON_EXTRACT_SCALAR(json_response, CONCAT('$.', ref_date, '.is_total')) != 'true'。

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