How to find pages in Confluence with a certain number of labels
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Purpose
This guide is to show Confluence administrators how to search for pages with labels associated with it. Note that the logic in the queries can be modified depending on the information you are trying to find.
Solution
The easiest way to find this information is through the database. Note that the following syntax was written for PostgreSQL and tested with Confluence version 5.8.13, but the syntax should be similar for other database flavors
Page with more than one label
Postgres query
SELECT c.title as PageTitle, count(title) as NumberOfLabels from content_label cl inner join label l on cl.labelid = l.labelid inner join content c on c.contentid = cl.contentid
where cl.labelabletype = 'CONTENT'
group by c.title
having COUNT(c.title) = 1;MySQL query
SELECT C.TITLE AS PAGETITLE, COUNT(TITLE) AS NUMBEROFLABELS FROM CONTENT_LABEL CL INNER JOIN LABEL L ON CL.LABELID = L.LABELID INNER JOIN CONTENT C ON C.CONTENTID = CL.CONTENTID
WHERE CL.LABELABLETYPE = 'CONTENT'
GROUP BY C.TITLE
HAVING COUNT(C.TITLE) = 1;Attachments with more than one label
Postgres query
SELECT c.title as PageTitle, count(title) as NumberOfLabels from content_label cl inner join label l on cl.labelid = l.labelid inner join content c on c.contentid = cl.contentid
where cl.labelabletype = 'ATTACHMENT'
group by c.title
having COUNT(c.title) = 1;MySQL query
SELECT C.TITLE AS PAGETITLE, COUNT(TITLE) AS NUMBEROFLABELS FROM CONTENT_LABEL CL INNER JOIN LABEL L ON CL.LABELID = L.LABELID INNER JOIN CONTENT C ON C.CONTENTID = CL.CONTENTID
WHERE CL.LABELABLETYPE = 'ATTACHMENT'
GROUP BY C.TITLE
HAVING COUNT(C.TITLE) = 1;