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

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.

CodePudding user response：

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