Question 89821 in PostgreSQL Question posted in
The official documentation can be found here.

Count and join with column type array in Postgres – Postgresql

I have two tables in my Postgres DB (v14), tags and locations.

Example of tags:

 id | tag_name      
----+------------
 1  | football
 2  | tennis
 3  | athletics
 4  | concert

Example of locations (where tag_ids is array of ints):

 id | name         | tag_ids      
----+--------------+------------
 1  | Wimbledon    | {2}
 2  | Wembley      | {1,4}
 3  | Letzigrund   | {3,4}

How can I find the name of the tags and how many times they are used? The query should result in something like this:

 tag_name   | count   
------------+-------
 football   | 1
 tennis     | 1
 athletics  | 1 
 concert    | 2
Tags:

2

Answers


  1. 0

    First flatten locations as t CTE and then join with tags.

    with t as
(
 select id, name, unnest(tag_ids) as tag_id from locations
 -- not all columns are needed, for illustration only
)
select tag_name, count(*) as count
from tags join t on t.tag_id = tags.id
group by tag_name;

    See demo.

    Login or Signup to reply.
  2. 0

    You can do this using a join and a GROUP BY:

    select t.tag_name, count(*)
from tags t
  join locations l on t.id = any(l.tag_ids)
group by t.tag_name
order by t.tag_name;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search