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

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
Powered by Confluence and Scroll Viewport.