PostgreSQL query for an aggregate count of distinct elements within an array

Question

I'd like to count the total number of occurrences of each unique tag in an array of strings across multiple rows and do it all as part of a PostgreSQL query.

I'm using Prisma, but I suspect if it is possible, I would have to do this via $queryRaw.

In my schema.prisma, allTags is set up as an array of strings:

  allTags                 String[]

This then got turned into a _text in the database schema, and the underscore seems to mean it's an array there:

    "allTags" _text,

So assuming data like this:

allTags
------------------------------
{JavaScript, Vue.js}
{JavaScript, Node}
{TypeScript, JavaScript, Node}

...the output should look something like this:

count + tag
------------------
3     | JavaScript
2     | Node
1     | Vue.js
1     | TypeScript

My hunch is that I might need to use a subquery here somehow (I've seen mention of LATERAL), but I can't quite piece it together.

LukStorms · Accepted Answer · 2021-12-25 14:24:09Z

2

Perhaps a lateral join to an unnesting of the array.

select count(*) as "count", tag
from your_table t
cross join lateral unnest(allTags) as tags(tag)
group by tag
order by 1 desc;

count	tag
3	JavaScript
2	Node
1	Vue.js
1	TypeScript

Demo on db<>fiddle here

answered Dec 25, 2021 at 14:24

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

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

1 Comment

Joost Schuur Over a year ago

Perfect, this is exactly what I was looking for and works. Thanks!

Collectives™ on Stack Overflow

PostgreSQL query for an aggregate count of distinct elements within an array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related