如何在BigQuery中以列值为键生成JSON或STRUCT类型字段？

嘿，我懂你的需求——你不想得到一堆包含category和features的数组元素，而是希望每个user_id对应的结果里，直接把各个category的值作为键，关联对应的info结构体，形成清晰的键值对映射对吧？

原来用ARRAY_AGG生成数组的方式确实达不到这个效果，因为它只能把数据按数组元素聚合，而你需要的是动态键值对结构。这里分两种场景给你解决方案：

场景1：category值不固定（动态变化） #

如果你的category可能有任意值，没办法提前枚举，那用JSON_OBJECT_AGG函数就最合适了。它可以直接把分组后的category作为键，对应的info作为值，聚合成一个JSON对象：

SELECT 
  user_id,
  JSON_OBJECT_AGG(category, info) AS features
FROM your_table_name
GROUP BY user_id

举个例子，user_1有cat_A和cat_B的话，features会是这样的JSON结构：

{
  "cat_A": {"f":2, "c":3},
  "cat_B": {"f":5, "c":1}
}

如果之后要访问某个category的具体字段，比如cat_A的f值，可以用BigQuery的JSON函数：

SELECT
  user_id,
  JSON_EXTRACT_SCALAR(features, '$.cat_A.f') AS cat_A_f
FROM (
  -- 上面的聚合查询
)

注意：如果同一个user_id+category有重复数据，JSON_OBJECT_AGG会默认保留最后一条的数据。如果需要控制重复数据的处理，可以结合ANY_VALUE或者MAX这类聚合函数，比如：

JSON_OBJECT_AGG(category, ANY_VALUE(info)) AS features

场景2：category值固定（已知所有可能的category） #

如果你的category只有固定的几个值（比如就cat_A、cat_B、cat_C），那用PIVOT来转置数据，再组合成STRUCT会更贴合BigQuery的结构化数据使用习惯：

SELECT
  user_id,
  STRUCT(
    cat_A.info AS cat_A,
    cat_B.info AS cat_B,
    cat_C.info AS cat_C
  ) AS features
FROM your_table_name
PIVOT (
  ANY_VALUE(info) AS info
  FOR category IN ('cat_A', 'cat_B', 'cat_C') -- 这里列出所有固定的category
)

这样生成的features是一个STRUCT，你可以直接通过features.cat_A.f来访问对应的字段，不需要处理JSON，更方便结构化查询。

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