BigQuery中如何将JSON字符串内的数组转置为独立列？

假设你的表有一个名为json_data的字符串字段，存储包含Categories数组的JSON，示例JSON结构如下：

{
  "id": "123",
  "product_name": "Wireless Headphones",
  "Categories": [
    {"cat_id": "001", "cat_name": "Audio"},
    {"cat_id": "002", "cat_name": "Electronics"}
  ]
}

固定结构数组处理（推荐） #

如果Categories数组内的元素结构固定，直接用UNNEST展开数组，同时提取所有字段：

SELECT
  -- 提取JSON中的非数组字段
  JSON_VALUE(json_data, '$.id') AS product_id,
  JSON_VALUE(json_data, '$.product_name') AS product_name,
  -- 从展开的数组元素中提取字段
  cat.cat_id,
  cat.cat_name
FROM
  `your-project.your-dataset.target-table`,
  -- 展开Categories数组，使用LEFT UNNEST避免丢失无分类的行
  LEFT UNNEST(PARSE_JSON(json_data).Categories) AS cat

非固定结构数组处理 #

如果Categories内的元素键不固定，先解析数组，再按需提取可能的键值：

WITH parsed_json AS (
  SELECT
    JSON_VALUE(json_data, '$.id') AS product_id,
    JSON_VALUE(json_data, '$.product_name') AS product_name,
    PARSE_JSON(json_data).Categories AS categories
  FROM `your-project.your-dataset.target-table`
),
unnested_cats AS (
  SELECT
    product_id,
    product_name,
    cat AS category_obj
  FROM parsed_json, LEFT UNNEST(categories) AS cat
)
SELECT
  product_id,
  product_name,
  JSON_VALUE(category_obj, '$.cat_id') AS cat_id,
  JSON_VALUE(category_obj, '$.cat_name') AS cat_name,
  JSON_VALUE(category_obj, '$.cat_tag') AS cat_tag -- 按需添加可能存在的键
FROM unnested_cats

关键说明 #

• 用PARSE_JSON将字符串转为BigQuery可操作的JSON类型，比旧版JSON_EXTRACT类函数更直观
• LEFT UNNEST保证原表中没有分类（数组为空）的行不会被过滤掉
• 若需要保留数值类型，对提取结果做CAST转换，比如CAST(JSON_VALUE(json_data, '$.price') AS NUMERIC) AS product_price

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