如何基于列值创建字段名动态的BigQuery Struct？

我目前想到两种可行思路，但都有局限：

• 使用STRING_AGG动态生成JSON，再转换为STRUCT，但STRUCT的约束性比JSON强，没法自动转换，得手动调整。
• 或许可以通过PIVOT结合SELECT AS STRUCT * EXCEPT()实现，但BigQuery没有合并STRUCT的聚合函数，分组透视时没法把列聚合为STRUCT。

现有源数据：

WITH cte AS (
  SELECT 1 AS id, "foo" AS category, "foo1" AS name, 10 AS value
  UNION ALL
  SELECT 1, "foo", "foo2", 20
  UNION ALL
  SELECT 1, "foo", "foo3", 30
  UNION ALL
  SELECT 1, "bar", "bar1", 100
  UNION ALL
  SELECT 1, "bar", "bar2", 200
)

期望输出：

请问这个需求能实现吗？是否需要用JavaScript UDF？

这个需求可以实现，不需要JavaScript UDF，用BigQuery原生SQL就能搞定，核心思路是通过条件聚合构造嵌套STRUCT后展开，或者用动态SQL生成目标列，以下是具体方案：

方案一：静态构造嵌套STRUCT（已知category和name取值） #

如果你的category和name取值是固定的，可以直接手动构建嵌套STRUCT结构，再通过.操作符展开成目标列：

WITH cte AS (
  SELECT 1 AS id, "foo" AS category, "foo1" AS name, 10 AS value
  UNION ALL
  SELECT 1, "foo", "foo2", 20
  UNION ALL
  SELECT 1, "foo", "foo3", 30
  UNION ALL
  SELECT 1, "bar", "bar1", 100
  UNION ALL
  SELECT 1, "bar", "bar2", 200
)
SELECT 
  id,
  -- 构造foo分类的嵌套STRUCT并展开
  (SELECT AS STRUCT 
     (SELECT AS STRUCT MAX(IF(name='foo1', value, NULL)) AS value) AS foo1,
     (SELECT AS STRUCT MAX(IF(name='foo2', value, NULL)) AS value) AS foo2,
     (SELECT AS STRUCT MAX(IF(name='foo3', value, NULL)) AS value) AS foo3
   FROM cte WHERE category='foo' AND id=t.id).*,
  -- 构造bar分类的嵌套STRUCT并展开
  (SELECT AS STRUCT 
     (SELECT AS STRUCT MAX(IF(name='bar1', value, NULL)) AS value) AS bar1,
     (SELECT AS STRUCT MAX(IF(name='bar2', value, NULL)) AS value) AS bar2
   FROM cte WHERE category='bar' AND id=t.id).*
FROM cte t
GROUP BY id

方案二：动态SQL生成（category和name取值不固定） #

如果category和name的取值是动态变化的，需要用BigQuery的脚本模式生成动态SQL来适配所有可能的列：

DECLARE sql STRING;

WITH cte AS (
  SELECT 1 AS id, "foo" AS category, "foo1" AS name, 10 AS value
  UNION ALL
  SELECT 1, "foo", "foo2", 20
  UNION ALL
  SELECT 1, "foo", "foo3", 30
  UNION ALL
  SELECT 1, "bar", "bar1", 100
  UNION ALL
  SELECT 1, "bar", "bar2", 200
),
category_name_pairs AS (
  SELECT DISTINCT category, name FROM cte
),
struct_clauses AS (
  SELECT 
    category,
    STRING_AGG(
      FORMAT('(SELECT AS STRUCT MAX(IF(name="%s", value, NULL)) AS value) AS %s', name, name),
      ', '
    ) AS struct_fields
  FROM category_name_pairs
  GROUP BY category
)
SELECT STRING_AGG(FORMAT('(SELECT AS STRUCT %s FROM cte WHERE category="%s" AND id=t.id).*', struct_fields, category), ', ')
INTO sql
FROM struct_clauses;

-- 拼接完整SQL并执行
SET sql = FORMAT(
  'WITH cte AS (%s) SELECT id, %s FROM cte t GROUP BY id',
  '(SELECT 1 AS id, "foo" AS category, "foo1" AS name, 10 AS value UNION ALL SELECT 1, "foo", "foo2", 20 UNION ALL SELECT 1, "foo", "foo3", 30 UNION ALL SELECT 1, "bar", "bar1", 100 UNION ALL SELECT 1, "bar", "bar2", 200)',
  sql
);

EXECUTE IMMEDIATE sql;

补充说明 #

• 你提到的JSON转换思路也能实现：先用STRING_AGG生成嵌套JSON字符串，再用JSON_EXTRACT_SCALAR提取对应字段，但性能不如原生STRUCT操作。
• BigQuery确实没有内置的STRUCT聚合函数，但通过子查询+条件聚合的方式，可以模拟实现嵌套STRUCT的构造逻辑。

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