BigQuery提取JSON多层嵌套中所有分类下的任务数据

针对动态分类（数量不固定）的JSON提取需求，需要先将tasks对象转换为键值对数组，再逐层展开处理，具体查询语句如下：

SELECT
  category_kv.key AS category_name,
  TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(task, '$.dateCompleted._seconds') AS INT64)) AS dateCompleted,
  JSON_EXTRACT_SCALAR(task, '$.slug') AS task_slug,
  JSON_EXTRACT_SCALAR(task, '$.status') AS status
FROM
  `table`,
  -- 将tasks对象转成键值对数组，每个元素包含分类名和对应任务数组
  UNNEST(JSON_QUERY_ARRAY(DATA, '$.tasks')) AS category_kv,
  -- 展开每个分类对应的任务数组
  UNNEST(JSON_EXTRACT_ARRAY(category_kv.value)) AS task

关键步骤说明： #

• JSON_QUERY_ARRAY(DATA, '$.tasks')：把tasks这个JSON对象转换为键值对数组，每个元素结构为{"key": "分类名", "value": [任务列表]}
• 第一次UNNEST：展开键值对数组，得到每个分类的名称和对应的任务数组
• 第二次UNNEST：展开每个分类的任务数组，拆分出单个任务的JSON对象
• 最后提取任务的各个字段，并将时间戳秒数转换为BigQuery的TIMESTAMP类型

如果需要保留原表的其他字段，直接在SELECT子句中添加对应的字段即可。

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