如何在BigQuery中展开非数组类型的JSON数据

你有如下结构的独立JSON事件：

{
"id":1234,
"data":{
    "packet1":{"name":"packet1", "value":1},
    "packet2":{"name":"packet2", "value":2}
     }
}

需要将其展开为每行对应一个packet的格式，目标结果如下：

之前尝试用UNNEST但该函数仅支持数组，无法直接处理data字段的JSON对象，且无法修改事件存储格式，需要在BigQuery内完成转换。

解决方案 #

BigQuery提供的OBJECT_TO_ARRAY函数可以将JSON对象转换为键值对数组，结合UNNEST就能实现需求。根据数据的存储形式，可选择以下两种方案：

方案1：处理原始JSON字符串字段 #

如果数据是以JSON字符串形式存储（比如字段名为event_data），使用以下SQL：

SELECT
  JSON_EXTRACT_SCALAR(event_data, '$.id') AS id,
  JSON_EXTRACT_SCALAR(packet.value, '$.name') AS name,
  JSON_EXTRACT_SCALAR(packet.value, '$.value') AS value
FROM
  `your-project.your-dataset.your-table`,
  UNNEST(OBJECT_TO_ARRAY(JSON_QUERY(event_data, '$.data'))) AS packet

方案2：处理已解析的STRUCT字段 #

如果表结构已将JSON解析为STRUCT类型（比如id为INT64，data为包含多个packet的STRUCT），使用更高效的SQL：

SELECT
  id,
  (packet_entry.value).name AS name,
  (packet_entry.value).value AS value
FROM
  `your-project.your-dataset.your-table`,
  UNNEST(OBJECT_TO_ARRAY(data)) AS packet_entry

原理说明 #

• OBJECT_TO_ARRAY(data) 将data对象转换为数组，每个元素是包含key（如"packet1"）和value（对应packet的STRUCT）的结构体
• UNNEST() 将数组展开为多行记录，每行对应一个packet
• 最后从展开后的结构体中提取name和value字段，同时保留原id

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