如何在CockroachDB中对JSONB键值对的数值求和？

我有一张表，每行包含JSONB格式的数据（为便于阅读用YAML展示）：

{ foo: 500, bar: 12 }
{ foo: 500, bar: 18, zoo: 5 }
...

这些JSONB对象的键是任意的，但都是字符串映射数值的结构。我希望将所有行的JSONB值按键求和，最终生成一个包含各键对应总和的JSONB。

目前我只能手动指定每个键来单独创建列实现：

SELECT 
    SUM((json->'foo')::INT) AS foo,
    SUM((json->'bar')::INT) AS bar,
    ...
  FROM test

需求：

• 有没有简洁的单SQL表达式方法能处理任意键？必要时可以创建函数。
• 性能优先，将用于数亿条数据，实际环境是CockroachDB（存在部分语法限制）。
• 源表需要分组，解决方案需支持多组数据的SUM操作。

解决方案1：原生SQL（无需自定义函数） #

利用CockroachDB支持的jsonb_each_text函数将JSONB拆分为键值对，分组聚合后再重新组合成JSONB，完全适配任意键和分组场景。

基础求和（无分组） #

SELECT jsonb_object_agg(key, SUM(value::INT)) AS total_json
FROM test, jsonb_each_text(test.json);

支持分组的版本 #

假设按group_id字段分组求和：

SELECT 
    group_id,
    jsonb_object_agg(key, SUM(value::INT)) AS total_json
FROM test, jsonb_each_text(test.json)
GROUP BY group_id;

说明

• jsonb_each_text将每行JSONB拆分为多行键值对，是实现动态键聚合的核心步骤。
• 先按分组字段+键维度求和，再通过jsonb_object_agg将聚合结果重新组合为JSONB。
• 性能优化建议：若分组字段是高频查询维度，可给分组字段创建索引，减少分组排序的开销；CockroachDB对JSON函数的行内操作优化较好，可先在小数据集验证后再扩量。

解决方案2：自定义聚合函数（超大规模数据优化） #

如果原生SQL的拆分-聚合步骤在数亿级数据下性能不足，可自定义JSONB求和聚合函数，减少中间数据生成，提升处理效率。

在CockroachDB中创建聚合函数 #

-- 定义状态转换函数：将输入JSONB的键值累加到聚合状态中
CREATE OR REPLACE FUNCTION jsonb_sum_state(state JSONB, input JSONB)
RETURNS JSONB AS $$
BEGIN
    IF state IS NULL THEN
        RETURN input;
    END IF;
    FOR key, value IN SELECT * FROM jsonb_each_text(input) LOOP
        state := state || jsonb_build_object(key, COALESCE((state->>key)::INT, 0) + value::INT);
    END LOOP;
    RETURN state;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 注册聚合函数
CREATE AGGREGATE jsonb_sum(input JSONB) (
    SFUNC = jsonb_sum_state,
    STYPE = JSONB,
    INITCOND = '{}'
);

使用自定义函数（支持分组） #

-- 无分组场景
SELECT jsonb_sum(json) AS total_json FROM test;

-- 按group_id分组场景
SELECT group_id, jsonb_sum(json) AS total_json FROM test GROUP BY group_id;

性能优势

自定义聚合函数无需拆分JSONB为中间行，直接在每行更新聚合状态，大幅减少IO和内存开销，更适合超大规模数据集的处理。

注意事项

• 确保CockroachDB版本在v22.2+，以支持完整的PL/pgSQL语法。
• 若JSONB中的值为浮点数，需将代码中的INT替换为FLOAT8以适配数值类型。

内容的提问来源于stack exchange，提问作者Ondra Žižka