计算数组或jsonb对象的频率 [英] Count freuency of array or jsonb object

问题描述

在pg中，有一个 varchar 类型的标签字段，其中包含以] 分隔的标签，例如'a] b] c'

In pg, there is a tags field of type varchar, containing tags separated by ], e.g 'a]b]c'.

需要统计这些标签在多行中的出现次数.

Need to count occurrences of these tags across multiple rows.

我知道该怎么做

• 将原始字符串转换为pg数组 ['a'，'b'，'c']
• 并且如果将该列指定为jsonb对象 {'a':1，'b':1，'c':1} ，则可以通过jsonb函数对频率进行计数.

• Convert the raw string into pg array ['a', 'b', 'c']
• and if the column is given as jsonb object {'a':1, 'b':1, 'c':1}, could count frequency via jsonb functions.

但是我不知道如何将pg数组 ['a'，'b'，'c'] 转换为jsonb对象 {'a':1，'b':1，'c':1} ，或直接对数组元素上的频率进行计数.

But I don't know how to convert pg array ['a', 'b', 'c'] into jsonb object {'a':1, 'b':1, 'c':1}, or just count frequency on array elements directly.

问题是:

• A.如何将文本'a] b] c'转换为jsonb对象 {'a':1，'b':1，'c':1} ，以及所有值为 1 .
• B.如何在多个行中计算数组元素 ['a'，'b'，'c'] 的频率.

• A. How to convert text 'a]b]c' into jsonb object {'a':1, 'b':1, 'c':1}, with all values as 1.
• B. How to count frequency of array elements ['a', 'b', 'c'], across multiple rows.

如果两个问题中的任何一个都可以解决，那么原来的问题就可以解决.
还是有更好的解决方案?

If either of the 2 questions can be solved, the original problem could be solved.
Or, there are even a better solution?

如果输入列已经是json对象，则不是原始字符串或数组.

If the input column is already json object, not raw string or array.

下面的表格显示了我想做什么:

Following table show what I want to do:

-- create table,
create table json_aggr_learn (
    id serial8 primary key,
    uid int8,
    freq jsonb,
    created_at timestamptz default current_timestamp
);

-- init data
insert into json_aggr_learn(uid, freq) values
(1, '{"a":1, "b":2}'),
(1,'{"b":2, "c":4}'),
(2, '{"a":1, "b":2}'),
(2,'{"b":7, "c":4}'),
(2,'{"e":10, "c":4}'),
(3,'{"a":5, "c":4, "f":2}');

select * from json_aggr_learn limit 5;

-- aggr
select uid, jsonb_object_agg(key, value) as merged_freq
from
     (select id, uid, key, value
      from json_aggr_learn, jsonb_each_text(freq)
     ) as expended
group by uid
order by uid;

这是aggr sql的输出:

Here is the output of aggr sql:

推荐答案

您可以 unnest()数组，例如:

select id, jsonb_object_agg(tag, count) as tags
from (
    select id, unnest(string_to_array(tags, ']')) as tag, count(*)
    from my_table
    group by 1, 2
    ) s
group by 1
order by 1

Db<>小提琴.

这篇关于计算数组或jsonb对象的频率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！