How to find pages in Confluence with a certain number of labels
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
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
Page with more than 1 label
1
2
3
4
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
Page with more than 1 label
1
2
3
4
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
Attachments with more than 1 label
1
2
3
4
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
Attachment with more than 1 label
1
2
3
4
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;Was this helpful?