Count number of distinct elements in varchar array Postgres

Question

I have a table.

title   | tags
--------------------------
post1   | {tag1,tag2,tag3}
post2   | {tag1,tag2}
post3   | {tag1}

It was made with this:

CREATE TABLE post (
    title varchar(10),
    tags varchar(10) array[5]
);

INSERT INTO post (title, tags) VALUES ('post1', '{"tag1", "tag2", "tag3"}');
INSERT INTO post (title, tags) VALUES ('post2', '{"tag1", "tag2"}');
INSERT INTO post (title, tags) VALUES ('post3', '{"tag1"}');

How could I count the number of distinct elements in the array for the whole table? For example, the tag tag1 appears in all three rows, so it would return 3. I want to do this for every element, creating an output that looks like this:

tag     | COUNT
--------------------------
tag1    | 3
tag2    | 2
tag3    | 1

The tags array will not have any duplicates.

GMB · Accepted Answer · 2020-04-27 22:20:01Z

3

You can unnest() the array, then aggregate:

select t.tag, count(*) no_posts
from post p
cross join lateral unnest(p.tags) t(tag)
group by t.tag
order by no_posts desc

Demo on DB Fiddle:

tag  | no_posts
:--- | -------:
tag1 |        3
tag2 |        2
tag3 |        1

answered Apr 27, 2020 at 22:20

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Count number of distinct elements in varchar array Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related