json - 如何在一个列上分组，在另一个列上聚合数组并创建一个由分组列键控的 JSON 对象

Question

使用此查询和记录集：

选择 id，级别 FROM t；

编号 | 等级
----------
1 | 一个
2 | 一个
3 | 一个
4 | b
5 | b
6 | C
7 | C

如何将其转换为 JSON 对象，以级别列和 id 列上聚合的数组为键？我事先不知道存在哪些级别。

{
  "a": [1, 2, 3],
  "b": [4, 5],
  "c": [6, 7]
}

我希望这样做：

SELECT json_object(
  array_agg(level),
  array_agg(ids)
)
FROM (
  SELECT level, array_agg(id::TEXT) ids
  FROM t
  GROUP BY level
) t

但它返回：

错误：无法累积不同维度的数组

如果您想回答，这里是t为了您的方便

SELECT id, level
FROM ( VALUES
  (1, 'a'),
  (2, 'a'),
  (3, 'a'),
  (4, 'b'),
  (5, 'b'),
  (6, 'c'),
  (7, 'c')
) t(id, level);

标签： jsonpostgresqlaggregateaggregate-functions

Answer 1

使用 json(b)聚合函数：

with my_table(id, level) as (
values
    (1, 'a'),
    (2, 'a'),
    (3, 'a'),
    (4, 'b'),
    (5, 'b'),
    (6, 'c'),
    (7, 'c')
)
select jsonb_object_agg(level, ids)
from (
    select level, jsonb_agg(id) as ids
    from my_table
    group by level
    ) s

              jsonb_object_agg              
--------------------------------------------
 {"a": [1, 2, 3], "b": [4, 5], "c": [6, 7]}
(1 row)