Count freuency of array or jsonb object

Question

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.

I know how to:

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.

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.

The questions are:

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?

@Update - To make the question more clear

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;

Here is the output of aggr sql:

Can you please give explanation to How to count frequency of array elements ['a', 'b', 'c'], across multiple rows. For Example, if 2 rows are there with row-1-col-1 = ['a','b','a'] and row-2-col1 = ['c', 'a', 'b'] what is the output you expect in terms of rows/columns? — axnet
– axnet, Commented Jan 19, 2021 at 3:43
@praxnet The expected output is {'a':2,'b':2,'c':2}, the order of element doesn't matter. I have updated the question with more info. — Eric
– Eric, Commented Jan 19, 2021 at 3:46

klin · Accepted Answer · 2021-01-19 04:02:09Z

1

You can unnest() arrays, e.g.:

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<>fiddle.

edited Jan 19, 2021 at 4:02

answered Jan 19, 2021 at 3:47

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Over a year ago

Could you also take a look at this question: stackoverflow.com/questions/65787512

Collectives™ on Stack Overflow

Count freuency of array or jsonb object

@Update - To make the question more clear

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

@Update - To make the question more clear

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related