如何在BigQuery中将含嵌套重复值的JSON列转为扁平表？

假设你的表名为your_table，存储JSON字符串的列名为json_column，可以通过两次UNNEST结合JSON解析函数实现需求，直接上代码：

SELECT
  JSON_EXTRACT_SCALAR(json_column, '$.activity') AS activity,
  JSON_EXTRACT_SCALAR(json_column, '$.device') AS device,
  JSON_EXTRACT_SCALAR(dataset_item, '$.date.activity_date') AS date,
  heartrate_item.max AS heartrate_max,
  heartrate_item.min AS heartrate_min,
  heartrate_item.name AS heartrate_name
FROM
  your_table,
  UNNEST(JSON_EXTRACT_ARRAY(json_column, '$.dataset')) AS dataset_item,
  UNNEST(JSON_EXTRACT_ARRAY(dataset_item, '$.value.heartrate')) AS heartrate_item

代码说明 #

• 用JSON_EXTRACT_SCALAR提取JSON顶层的activity和device字段
• 第一次UNNEST展开dataset数组，获取每个子项内容
• 第二次UNNEST展开每个dataset子项里的heartrate数组，把每条心率记录拆成独立行
• 最后提取activity_date以及心率的max/min/name字段

执行后得到的结果如下：

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