将行合并到jsonb对象中 [英] Merge rows into jsonb object

问题描述

(这是

预期的输出为:

  uid tag_freq1 {'a':2，'b':1，'c':1}2 {'a':1，'b':2，'c':2，'e':1}... 

输出列 tag_freq 是jsonb对象，它是用户的合并结果.

有什么办法可以编写这样的查询?

解决方案

您可以使用Postgresql - Count freuency of array or jsonb object)

In postgresql 12+, given following input rows:

The expected output is:

uid   tag_freq

1     {'a':2, 'b':1, 'c':1}
2     {'a':1, 'b':2, 'c':2, 'e':1}
...

Output column tag_freq is jsonb object, and it's merged result for a user.

Is there any way to write such a query?

解决方案

You can use jsonb_object_agg() for this:

select uid, jsonb_object_agg(tag, count) as tag_freq
from the_table
group by uid;

这篇关于将行合并到jsonb对象中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！