如何在BigQuery中将键值对数组转换为结构化表格？

假设你已经有包含键值对数组的表（比如字段名为kv_array），可以通过UNNEST展开数组 + 拆分键值 + PIVOT行转列的步骤实现需求，以下是具体方案：

示例完整SQL #

-- 替换为你的实际表名和字段名
WITH log_data AS (
  -- 模拟你的原始数据结构
  SELECT ["user_id: 1342", "foo: bar", "baz: xxx"] AS kv_array UNION ALL
  SELECT ["user_id: 4312", "foo: ds", "baz: dss"]
),
-- 第一步：展开数组并拆分键和值
kv_rows AS (
  SELECT
    -- 用唯一标识分组，避免不同日志的键值对混淆；如果有原始主键（如日志ID）可替换此处
    ROW_NUMBER() OVER() AS row_id,
    SPLIT(kv_pair, ': ')[OFFSET(0)] AS key,
    SPLIT(kv_pair, ': ')[OFFSET(1)] AS value
  FROM log_data, UNNEST(kv_array) AS kv_pair
)
-- 第二步：将行转成结构化列
SELECT
  row_id, -- 可根据需求删除
  user_id,
  foo,
  baz
FROM kv_rows
PIVOT(
  MAX(value) FOR key IN ('user_id', 'foo', 'baz')
)

关键步骤说明 #

• UNNEST展开数组：UNNEST(kv_array)将每个数组元素拆分为单独的行，让每个键值对成为一条记录；
• 拆分键值对：用SPLIT(kv_pair, ': ')按「冒号+空格」拆分键和值，如果你的日志中分隔符有空格差异，可结合TRIM处理：TRIM(SPLIT(kv_pair, ':')[OFFSET(1)])；
• 唯一行标识：ROW_NUMBER()生成的row_id用于确保每一条原始日志的键值对能正确分组，避免不同日志的字段被错误合并；如果你的表已有唯一主键（如日志时间戳、日志ID），直接使用该主键更准确；
• PIVOT行转列：通过PIVOT将key列的不同取值转换为表格列，MAX(value)是因为每个键在单条日志中只会出现一次，使用MAX/MIN不影响结果。

特殊情况处理 #

如果部分日志缺少某个键（比如没有baz字段），PIVOT后对应的列会显示NULL，可以用IFNULL设置默认值：

SELECT
  row_id,
  user_id,
  foo,
  IFNULL(baz, 'N/A') AS baz
FROM kv_rows
PIVOT(
  MAX(value) FOR key IN ('user_id', 'foo', 'baz')
)

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