如何在BigQuery中解析无数组结构的嵌套JSON？

假设你的原表为your_dataset.your_table，存储字符串化JSON的列名为json_column，可以通过以下SQL实现需求：

SELECT
  id,
  CAST(JSON_EXTRACT_SCALAR(json_column, CONCAT('$.', id, '.age')) AS INT64) AS age,
  JSON_EXTRACT_SCALAR(json_column, CONCAT('$.', id, '.size')) AS size
FROM
  your_dataset.your_table,
  UNNEST(JSON_KEYS(json_column)) AS id

代码说明： #

• JSON_KEYS(json_column)：提取JSON对象中的所有顶级键（即示例中的id_1、id_2），返回字符串数组。
• UNNEST(...) AS id：将数组展开为多行，每行对应一个独立的id值。
• JSON_EXTRACT_SCALAR(json_column, CONCAT('$.', id, '.age'))：根据当前id拼接JSON路径，提取对应的age字段值，SCALAR确保返回单个字符串值，再通过CAST转换为整数类型。
• JSON_EXTRACT_SCALAR(json_column, CONCAT('$.', id, '.size'))：同理提取size字段值。

测试示例： #

如果需要快速验证，可以用WITH子句模拟原表数据：

WITH sample_data AS (
  SELECT '''{
    "id_1": {
        "age": "10",
        "size": "M"
    },
    "id_2": {
        "age": "20",
        "size": "L"
    }
  }''' AS json_column
)
SELECT
  id,
  CAST(JSON_EXTRACT_SCALAR(json_column, CONCAT('$.', id, '.age')) AS INT64) AS age,
  JSON_EXTRACT_SCALAR(json_column, CONCAT('$.', id, '.size')) AS size
FROM
  sample_data,
  UNNEST(JSON_KEYS(json_column)) AS id

运行后会得到你需要的结构化表结果。

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