如何在BigQuery中将JSON结构体转换为行数据？

可以直接通过BigQuery SQL实现需求，以下是两种高效的解决方案：

方法一：利用JSON_OBJECT_KEYS提取键并匹配值 #

这是最直接的方式，无需修改JSON结构，通过内置函数直接解析键值对：

SELECT
  Date_field,
  currency_code,
  SAFE_CAST(JSON_VALUE(JSON_field, CONCAT('$.', currency_code)) AS FLOAT64) AS exchange_rate
FROM
  `你的项目ID.你的数据集ID.你的表名`,
  UNNEST(JSON_OBJECT_KEYS(JSON_field)) AS currency_code

说明： #

• JSON_OBJECT_KEYS(JSON_field)：提取JSON对象中所有的币种代码（如AED、AFN），返回一个数组
• UNNEST(...)：将数组拆分成单行数据，每个币种代码对应一行
• JSON_VALUE(JSON_field, CONCAT('$.', currency_code))：通过动态拼接的JSON路径，获取对应币种的汇率值
• SAFE_CAST(...) AS FLOAT64：安全转换为数值类型，避免因格式错误导致查询失败

方法二：将JSON对象转换为数组后展开 #

如果需要更结构化的数组形式，可以先将JSON对象转换为数组格式再解析：

SELECT
  Date_field,
  JSON_VALUE(item, '$.code') AS currency_code,
  SAFE_CAST(JSON_VALUE(item, '$.rate') AS FLOAT64) AS exchange_rate
FROM
  `你的项目ID.你的数据集ID.你的表名`,
  UNNEST(
    JSON_EXTRACT_ARRAY(
      REGEXP_REPLACE(
        JSON_field,
        r'"([^"]+)":([^,}]+)',
        '{"code":"$1","rate":$2}'
      )
      -- 将对象的大括号替换为数组的中括号
      |> REPLACE('{', '[')
      |> REPLACE('}', ']')
    )
  ) AS item

说明： #

• REGEXP_REPLACE：将原JSON的键值对（如"AED":3.6725）转换为结构化对象{"code":"AED","rate":3.6725}
• REPLACE：将外层的{}替换为[]，把JSON对象转为JSON数组
• JSON_EXTRACT_ARRAY：解析转换后的数组，再通过UNNEST拆分每行数据

两种方法都能实现将JSON字段展开为每行对应一个币种和汇率的结构，推荐使用方法一，性能更优且逻辑更简洁。

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