如何在BigQuery/GoogleSQL中将键值对数组转换为表列

在BigQuery中无需提前指定所有key名、自动将键值对数组转为多列的需求，可通过EXECUTE IMMEDIATE执行动态生成的SQL实现，具体方案如下：

方案1：子查询逐行匹配key取值 #

适合数组中每个key在单一行内唯一无重复的场景：

EXECUTE IMMEDIATE format("""
SELECT
  -- 可在此处补充需要保留的原表其他字段，例如 id, create_time 等
  %s
FROM mySource
""",
-- 自动提取所有不重复的key，生成对应字段的查询逻辑
(
  SELECT STRING_AGG(
    FORMAT("(SELECT value FROM UNNEST(params) WHERE key = '%s') AS %s", key, key)
  )
  FROM (
    SELECT DISTINCT key
    FROM mySource, UNNEST(params)
  )
))

方案2：UNNEST后配合PIVOT转列 #

适合单一行内可能存在重复key、需要自定义聚合规则的场景，示例中用MAX处理重复值，可根据需求替换为MIN、ARRAY_AGG等聚合函数：

EXECUTE IMMEDIATE format("""
SELECT * FROM (
  SELECT 
    -- 可在此处补充需要保留的原表其他字段，例如 t.id, t.create_time 等
    p.key, p.value
  FROM mySource t, UNNEST(params) p
) PIVOT(
  MAX(value) FOR key IN (%s)
)
""",
-- 自动提取所有不重复的key生成PIVOT的枚举值
(SELECT STRING_AGG(FORMAT("'%s'", key)) FROM (SELECT DISTINCT key FROM mySource, UNNEST(params)))
)

注意：两种方案都会自动扫描全表的所有key值生成列，若数据量较大可先对key的范围做过滤，减少不必要的扫描开销。

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