PostgreSQL 14：统计数组类型标签列使用次数的查询方法

表结构说明 #

tags表 #

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

locations表（tag_ids为整数数组类型） #

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

查询语句 #

要获取各标签名称及被使用次数，可通过两种方式实现：

方式一：利用ANY()关联数组元素 #

SELECT t.tag_name, COUNT(*) AS count
FROM tags t
JOIN locations l ON t.id = ANY(l.tag_ids)
GROUP BY t.tag_name
ORDER BY t.tag_name;

方式二：用unnest()展开数组为行后关联 #

SELECT t.tag_name, COUNT(*) AS count
FROM locations l
CROSS JOIN unnest(l.tag_ids) AS tag_id
JOIN tags t ON t.id = tag_id
GROUP BY t.tag_name
ORDER BY t.tag_name;

预期查询结果 #

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

内容的提问来源于stack exchange，提问作者Pål Simen Ellingsen