CODEWITHSUNDEEP
sqldatabasepostgresqlprisma
Joost Schuur
Joost Schuur

Reputation: 4482

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

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.

Upvotes: 0

Views: 689

Answers (1)

LukStorms
LukStorms

Reputation: 29647

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

Upvotes: 2

Related Questions