PostgreSQL 10中如何聚合JSONB数据？

当然可以实现！在PostgreSQL里，我们可以利用内置的JSONB函数轻松完成按ID分组合并JSONB对象的需求，具体方案如下：

核心思路 #

你的每一行数据里的JSONB都是单个键值对（比如{"a": {...}}或{"b": {...}}），我们可以先把这些键和对应的值拆分出来，再按ID分组聚合，将同ID下的所有键值对合并成一个完整的JSONB对象。

具体SQL语句 #

假设你的表名为your_table，ID列是id，JSONB列是json_data，可以用以下查询：

SELECT
    id,
    jsonb_object_agg(key, value) AS merged_json
FROM
    your_table,
    jsonb_each(json_data)
GROUP BY
    id
ORDER BY
    id;

语句解释 #

• jsonb_each(json_data)：这个函数会把每行的JSONB对象拆分成(key, value)的行记录，比如对于{"a": {"per": 0.45, "number": 1}}，会拆出key='a'，value='{"per": 0.45, "number": 1}'。
• GROUP BY id：按ID对数据进行分组，确保同ID的所有行被聚合到一起。
• jsonb_object_agg(key, value)：将同ID下的所有key-value对聚合为一个完整的JSONB对象，正好符合你想要的合并效果。

测试验证 #

我们可以用你提供的数据创建临时表测试：

-- 创建临时表
CREATE TEMP TABLE temp_data (
    id INT,
    json_data JSONB
);

-- 插入测试数据
INSERT INTO temp_data VALUES
(1, '{"a" : {"per" : 0.45, "number" : 1}}'),
(1, '{"b" : {"per" : 3.13, "number" : 7}}'),
(2, '{"a" : {"per" : 14.73, "number" : 33}}'),
(2, '{"b" : {"per" : 85.27, "number" : 191}}'),
(3, '{"a" : {"per" : 40.63, "number" : 91}}'),
(4, '{"a" : {"per" : 1.79, "number" : 4}}'),
(4, '{"b" : {"per" : 2.68, "number" : 6}}');

-- 执行查询
SELECT
    id,
    jsonb_object_agg(key, value) AS merged_json
FROM
    temp_data,
    jsonb_each(json_data)
GROUP BY
    id
ORDER BY
    id;

执行后得到的结果完全符合你的需求：

id |                                                                 merged_json
----+--------------------------------------------------------------------------------------------------------------------------------------------
  1 | {"a": {"per": 0.45, "number": 1}, "b": {"per": 3.13, "number": 7}}
  2 | {"a": {"per": 14.73, "number": 33}, "b": {"per": 85.27, "number": 191}}
  3 | {"a": {"per": 40.63, "number": 91}}
  4 | {"a": {"per": 1.79, "number": 4}, "b": {"per": 2.68, "number": 6}}

注意事项 #

如果同ID下存在重复的键（比如两行都有"a"键），jsonb_object_agg会保留最后一条出现的键值对。如果需要处理这种情况，可以根据业务需求调整逻辑（比如求和number或取平均per等）。

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