如何在BigQuery中将含动态键的JSON列转换为结构化表格？

针对你描述的场景（JSON列顶层是动态数字ID，每个ID对应包含固定字段的对象），可以通过UNNEST结合JSON_KEYS动态解析键值对，再提取对应字段。以下是可行的SQL方案：

基础实现方案 #

SELECT
  doc_id,
  id AS `id`,
  -- 提取字符串类型字段，非字符串类型可改用JSON_EXTRACT后CAST
  JSON_EXTRACT_SCALAR(data, CONCAT('$."', id, '"."author"')) AS author,
  JSON_EXTRACT_SCALAR(data, CONCAT('$."', id, '"."new"')) AS `new`,
  JSON_EXTRACT_SCALAR(data, CONCAT('$."', id, '"."old"')) AS old,
  JSON_EXTRACT_SCALAR(data, CONCAT('$."', id, '"."property"')) AS property,
  JSON_EXTRACT_SCALAR(data, CONCAT('$."', id, '"."sender"')) AS sender
FROM
  -- 替换成你的表名
  `your-project.your-dataset.your-table`,
  -- 拆分JSON顶层的所有动态键为单独行
  UNNEST(JSON_KEYS(data)) AS id

更清晰的进阶方案（先提取对象再拆字段） #

如果JSON内字段较多，先提取整个ID对应的对象再拆字段会更易维护：

SELECT
  doc_id,
  id AS `id`,
  JSON_EXTRACT_SCALAR(obj, '$.author') AS author,
  JSON_EXTRACT_SCALAR(obj, '$.new') AS `new`,
  JSON_EXTRACT_SCALAR(obj, '$.old') AS old,
  JSON_EXTRACT_SCALAR(obj, '$.property') AS property,
  JSON_EXTRACT_SCALAR(obj, '$.sender') AS sender
FROM
  `your-project.your-dataset.your-table`,
  UNNEST(JSON_KEYS(data)) AS id,
  -- 先提取当前ID对应的完整JSON对象
  UNNEST([STRUCT(JSON_QUERY(data, CONCAT('$."', id, '"')) AS obj)])

关键说明 #

• 动态数字键必须用双引号包裹在JSON路径中，所以用CONCAT拼接路径字符串，避免解析报错
• 如果字段是数字、布尔等非字符串类型，将JSON_EXTRACT_SCALAR改为JSON_EXTRACT后用CAST转换类型，例如：

  CAST(JSON_EXTRACT(data, CONCAT('$."', id, '"."new"')) AS INT64) AS `new`
  

• 之前用JSON_EXTRACT失败的原因是无法直接指定动态变化的顶层键，必须先通过JSON_KEYS拆分出所有键，再逐行解析对应字段

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