I have a column of text[]. How do I get a frequency count of all the objects across the column?
Example:
col_a
--------
{a, b}
{a}
{b}
{a}
Output should be:
col_a | count
----------------
a | 3
b | 2
My query:
with all_tags as (
select array_agg(c)
from (
select unnest(tags)
from message_tags
) as dt(c)
)
select count(*) from all_tags;
figured it out:
-- Collapse all tags into one array
with all_tags as (
select array_agg(c) as arr
from (
select unnest(ner_tags)
from message_tags
) as dt(c)
),
-- Expand single array into a row per tag
row_tags as (
select unnest(arr) as tags from all_tags
)
-- count distinct tags
select tags, count(*) from row_tags group by tags
As an alternative, you could just skip several steps and directly group on the unnested value:
select unnest(ner_tags) as tags,
count(*) as cnt
from message_tags
group by tags
order by cnt desc
Since you only require a count over each of the values (no distinct or other aggregates), this is the simplest solution.
