postgresql - Postgres 在 map 中连接不同的键

Question

Postgres 9.6 中的哪些 jsonb 函数组合可用于聚合映射的不同键？ (同一键的不同值会导致该键的随机值。)

例如鉴于此数据:

WITH tbl(id, j) as (
values
    (1, '{"key1": "val1"}'::jsonb),
    (1, '{"key2": "val2"}'),
    (1, '{"key2": "val2"}'),
    (2, '{"key3": "val3"}')
)
SELECT id, <what aggregate fn?>(data) FROM tbl GROUP BY 1

我们怎样才能像这样返回一个经过去重和合并的结果集？

id  | j
----|----------------------------------
1   | {"key1": "val1", "key2": "val2"}
2   | {"key3": "val3"}

Answer 1

使用 jsonb_object_agg() 对 (key, value) 从 jsonb_each() 中获取，用于横向连接:

with tbl(id, j) as (
values
    (1, '{"key1": "val1"}'::jsonb),
    (1, '{"key2": "val2"}'),
    (1, '{"key2": "val2"}'),
    (2, '{"key3": "val3"}')
)
select id, jsonb_object_agg(key, value)
from tbl 
cross join jsonb_each(j)
group by 1

 id |         jsonb_object_agg         
----+----------------------------------
  1 | {"key1": "val1", "key2": "val2"}
  2 | {"key3": "val3"}
(2 rows)

---

更新。您可以创建自定义聚合:

create or replace function jsonb_object_merge(jsonb, jsonb)
returns jsonb language plpgsql as $$
begin
    return jsonb_object_agg(key, value)
    from ( 
        select key, value from jsonb_each($2)
        union   
        select key, value from jsonb_each($1)
    ) s;
end $$;

create aggregate my_jsonb_object_agg(jsonb)
(
    sfunc = jsonb_object_merge,
    stype = jsonb
);

这可能在更复杂的查询中有用(连同其他聚合):

with tbl(id, j, v) as (
values
    (1, '{"key1": "val1", "key4": "val4"}'::jsonb, 1),
    (1, '{"key2": "val2"}', 1),
    (1, '{"key2": "val3"}', 1),
    (2, '{"key3": "val3"}', 1)
)
select id, my_jsonb_object_agg(j), sum(v)
from tbl
group by 1

 id |               my_jsonb_object_agg                | sum 
----+--------------------------------------------------+-----
  1 | {"key1": "val1", "key2": "val2", "key4": "val4"} |   3
  2 | {"key3": "val3"}                                 |   1
(2 rows)