在BigQuery中展平嵌套JSON数据至表格的SQL方案

针对你的需求，需要两次嵌套UNNEST来逐层展开dataset和heartRate数组，同时提取关联字段。假设你的表名为your_table，存储JSON的列名为json_data（请根据实际情况替换），对应的SQL语句如下：

SELECT
  json_data.activity AS activity,
  dataset_item.time._seconds AS time,
  -- customHeartRateZones为空数组，返回null
  NULL AS v_customheartrate,
  heart_rate_item.max AS v_heartrate_max,
  heart_rate_item.min AS v_heartrate_min,
  heart_rate_item.name AS v_heartrate_name
FROM
  your_table,
  -- 展开dataset数组
  UNNEST(json_data.dataset) AS dataset_item,
  -- 展开dataset下的heartRate子数组
  UNNEST(dataset_item.value.heartRate) AS heart_rate_item

适配STRING类型JSON列的版本 #

如果你的JSON列是STRING类型（而非BigQuery原生JSON类型），需要用JSON解析函数调整语句：

SELECT
  JSON_EXTRACT_SCALAR(json_data, '$.activity') AS activity,
  JSON_EXTRACT_SCALAR(dataset_item, '$.time._seconds') AS time,
  NULL AS v_customheartrate,
  JSON_EXTRACT_SCALAR(heart_rate_item, '$.max') AS v_heartrate_max,
  JSON_EXTRACT_SCALAR(heart_rate_item, '$.min') AS v_heartrate_min,
  JSON_EXTRACT_SCALAR(heart_rate_item, '$.name') AS v_heartrate_name
FROM
  your_table,
  UNNEST(JSON_QUERY_ARRAY(json_data, '$.dataset')) AS dataset_item,
  UNNEST(JSON_QUERY_ARRAY(dataset_item, '$.value.heartRate')) AS heart_rate_item

执行以上语句后，会生成你预期的结果：每个heartRate数组元素对应一行记录，同时保留activity、time等关联字段，v_customheartrate因原JSON中customHeartRateZones为空数组显示为null。

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