Sample Queries - Confluence Labels

Owned by Former user (Deleted)
Last updated: Jul 03, 2019
1 min read

These queries works with PostgreSQL and were tested against Confluence 5.10. You will need to adjust slightly for other databases. As with all queries against a product database, you will 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