Sample queries - Confluence labels

Owned by Bob Swift
Last updated: Mar 02, 2020 by Smita Nair
1 min read

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

Log a request with our support team.

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