如何在Google BigQuery中提取并展开嵌套JSON字段？

一、提取单个JSON字段 #

BigQuery用JSON_EXTRACT_SCALAR提取字符串/数值类型的JSON字段，JSON_EXTRACT提取JSON对象或数组。

假设你的表jsonsenthil.json.tvjson1有一个存储JSON的字段（比如json_content），示例JSON结构如下：

{
  "show_id": "SH001",
  "show_name": "Tech Talk",
  "release_year": 2024
}

提取字段的SQL代码：

SELECT
  JSON_EXTRACT_SCALAR(json_content, '$.show_id') AS show_id,
  JSON_EXTRACT_SCALAR(json_content, '$.show_name') AS show_name,
  CAST(JSON_EXTRACT_SCALAR(json_content, '$.release_year') AS INT64) AS release_year
FROM `jsonsenthil.json.tvjson1`

二、展开嵌套数组元素 #

如果JSON包含嵌套数组，结合UNNEST和JSON_EXTRACT_ARRAY就能展开数组元素。

示例嵌套JSON结构：

{
  "show_id": "SH001",
  "show_name": "Tech Talk",
  "episodes": [
    {"episode_id": "EP001", "title": "Intro to BigQuery", "duration": 30},
    {"episode_id": "EP002", "title": "JSON Processing", "duration": 25}
  ]
}

展开嵌套数组的SQL代码：

SELECT
  JSON_EXTRACT_SCALAR(json_content, '$.show_id') AS show_id,
  JSON_EXTRACT_SCALAR(json_content, '$.show_name') AS show_name,
  JSON_EXTRACT_SCALAR(episode, '$.episode_id') AS episode_id,
  JSON_EXTRACT_SCALAR(episode, '$.title') AS episode_title,
  CAST(JSON_EXTRACT_SCALAR(episode, '$.duration') AS INT64) AS episode_duration
FROM `jsonsenthil.json.tvjson1`,
UNNEST(JSON_EXTRACT_ARRAY(json_content, '$.episodes')) AS episode

常见问题说明 #

SQL Server里的OPENJSON对应BigQuery的UNNEST(JSON_EXTRACT_ARRAY())组合，别直接沿用SQL Server语法。如果你的无效代码用了类似OPENJSON的写法，替换成上述BigQuery专属语法即可。

内容的提问来源于stack exchange，提问作者Senthil P Nathan