BigQuery中如何按JSON列键值对聚合求和？

算力阿峰

2026-6-18

在BigQuery中按JSON键值对分组求和的实现

需求说明

我们有一张BigQuery表，包含cost（浮点型）和labels（JSON型）两列，数据示例如下：

| cost Float | labels JSON | 
| 10         | {"key1":"value1", "key2":"vaue2"} |
| 20         | {"key1":"value3", "key2":"vaue2", "key3":"vaue4"} |
....

需要对全表中所有唯一的键值对组合，计算对应的cost总和，期望输出示例：

total_cost  key_value_pair
10          "key1:value1"
20          "key1:value3"
30          "key2:vaue2"
20          "key3:vaue4"

问题重现

尝试了以下SQL语句：

SELECT
    t.key,
    t.value,
    SUM(t.cost) AS total_cost
  FROM
    (
      SELECT
          CAST(key AS STRING) AS key,
          JSON_EXTRACT_SCALAR(label, CONCAT('$[', key, ']')) AS value,
          cost
        FROM
          `PlayGround.table1`,
          UNNEST(GENERATE_ARRAY(1, CAST(JSON_EXTRACT_SCALAR(label, '$[0]') AS BIGNUMERIC))) AS key
    ) AS t
  GROUP BY 1, 2

执行时触发报错：JSON_EXTRACT_SCALAR的第二个参数必须是常量表达式

解决方案

该需求完全可以在BigQuery中实现。报错原因是JSON_EXTRACT_SCALAR的路径参数要求是常量，不能用动态生成的变量。正确的做法是借助JSON_QUERY_ARRAY将JSON对象转换为键值对数组，再展开处理：

方案一：直接生成键值对字符串并求和

SELECT
  CONCAT(kv.key, ':', kv.value) AS key_value_pair,
  SUM(cost) AS total_cost
FROM
  `PlayGround.table1`,
  UNNEST(JSON_QUERY_ARRAY(labels, '$')) AS kv
GROUP BY
  key_value_pair
ORDER BY
  total_cost;

方案二：保留key和value字段再组合

如果需要单独保留key和value字段，可使用以下SQL：

SELECT
  kv.key,
  kv.value,
  SUM(cost) AS total_cost,
  CONCAT(kv.key, ':', kv.value) AS key_value_pair
FROM
  `PlayGround.table1`,
  UNNEST(JSON_QUERY_ARRAY(labels, '$')) AS kv
GROUP BY
  kv.key, kv.value
ORDER BY
  total_cost;