Summary

This document will help you to find the list of all Space categories in Confluence with the help of SQL query.

About Space Category

If you've got lots of related spaces, you can use labels to group them together into categories in the Space Directory. For example, if you're in an IT team who works on a number of projects, each in a different space, you could label each space 'IT-projects-open'. Then in the Space Directory, you could select IT-projects-open to see all your current IT project spaces. 

You can add as many space categories to a space as you need, so that if, for example, two different teams are working on a project together, you can add labels for both teams and space will appear under both categories.

Labels are easy to add or remove, so if your needs change, you can always recategorize your spaces. 

Solution

• Space category is stored in Label table in Confluence. So the below query will help you fetch the list of all Space categories in Confluence
  
  

SELECT  name as Category FROM LABEL where namespace = 'team';

• To find the list of Categories and to what space it belongs , we can run the below query:

select
      NAME as "Category", SPACEKEY, SPACENAME as "Space" 
from CONTENT_LABEL
join LABEL
    on CONTENT_LABEL.LABELID = LABEL.LABELID
join CONTENT
    on CONTENT_LABEL.CONTENTID = CONTENT.CONTENTID
join SPACES
    on CONTENT.SPACEID = SPACES.SPACEID
WHERE namespace = 'team';