Sample queries - Confluence labels

These queries works with PostgreSQL and were tested against Confluence 5.10. You may need to adjust slightly for other databases. As with all queries against a product database, you need to define a new read-only data source to access the product's database.

Thanks to Shiran Kleiderman for the initial query and question on extending.

Site labels

select
    name as "Label"
  , count(name) as "Count" 
from CONTENT_LABEL 
left join LABEL 
    on CONTENT_LABEL.labelid = LABEL.labelid 
group by name 
order by name

Labels for space

select
    name as "Label"
  , count(name) as "Count" 
from CONTENT_LABEL 
left join LABEL 
    on CONTENT_LABEL.labelid = LABEL.labelid 
left join CONTENT 
    on CONTENT_LABEL.contentid = CONTENT.contentid
left join SPACES 
    on CONTENT.spaceid = SPACES.spaceid 
    where spacekey in ('ds')
group by name 
order by name

Labels by space

select
    spacename as "Space"
  , name as "Label"
  , count(name) as Count 
from CONTENT_LABEL 
left join LABEL 
    on CONTENT_LABEL.labelid = LABEL.labelid 
left join CONTENT 
    on CONTENT_LABEL.contentid = CONTENT.contentid
left join SPACES 
    on CONTENT.spaceid = SPACES.spaceid 

group by spacename, name 
order by spacename, name


Find answers from the community.

Ask a question to the community.

Log a request with our support team.


Confluence®, Jira®, Atlassian Bamboo®, Bitbucket®, Fisheye®, and Atlassian Crucible® are registered trademarks of Atlassian®
Copyright © 2005 - 2022 Appfire | All rights reserved. Appfire™, the 'Apps for makers™' slogan and Bob Swift Atlassian Apps™ are all trademarks of Appfire Technologies, LLC.