How to find pages in Confluence with a certain number of labels

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

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 ad tested with Confluence version 5.8.13, but the syntax should be similar for other database flavours

    Page with more than 1 label 
    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;
    Page with more than 1 label 
    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 1 label 
    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;
    Attachment with more than 1 label 
    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;

Last modified on Jun 22, 2023

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.