Confluence Server DB - Looking for Space Category

[Paul Madison]

Where can I find the Space Category in the database?

[Matt · Accepted solution]

Does it have to be DB? The schema doc doesn't clearly show the relation between the 'label' table and the 'space' table.

https://confluence.atlassian.com/doc/confluence-data-model-127369837.html

You can execute this as a starting point:

select * from label where NAMESPACE = "team"

However, I'm not 100% sure that labels in the 'global' namespace cannot also be used on spaces. So, I don't know if the above query returns a complete dataset. This query gets both global and space labels but not page-specific ones.

select * from label where NAMESPACE = "team" or NAMESPACE = "global"

Either way, those queries only give you what labels have been added and does not show any relation to what they were added to, which may be what you're actually looking for.

It's fairly trivial to get that data from the REST API and then parse it as needed:

1. Get a list of all of the space keys either from the DB or from the API:

   # DB
   select spacekey from spaces
   # API - you'll have to paginate through the 
   https://<confluence-url>/rest/api/space?limit=500

2. Take the list of space keys returned and for each one send a request to this endpoint:

   https://<confluence-url>/rest/api/space?spaceKey=<SPACEKEY>&expand=metadata.labels

Your output will look something like this for each request:

{
"results": [
{
"id": "<SPACE-ID>",
"key": "<SPACE-KEY>",
"name": "<SPACE-TITLE>",
"type": "global",
"metadata": {
"labels": {
"results": [
{
"prefix": "team",
"name": "<LABEL-NAME>",
"id": "<LABEL-ID>"
},
{
"prefix": "team",
"name": "<LABEL-NAME>",
"id": "<LABEL-ID>"
},
{
"prefix": "team",
"name": "<LABEL-NAME>",
"id": "<LABEL-ID>"
}
],
"start": 0,
"limit": 200,
"size": 3
}
},
"_links": {
"webui": "/display/<SPACE-KEY>",
"self": "https://<confluence-url>/rest/api/space/<SPACE-KEY>"
},
"_expandable": {
"icon": "",
"description": "",
"homepage": "/rest/api/content/<SPACE-HOME-ID>"
}
}
],
"start": 0,
"limit": 25,
"size": 1,
"_links": {
"self": "https://<confluence-url>/rest/api/space?spaceKey=<SPACE-KEY>&expand=metadata.labels",
"base": "https://<confluence-url>",
"context": ""
}
}

Hopefully that's a starting point for you. Good luck!

[Paul Madison]

Thanks for the detailed response.

This at least gives me one avenue to get what I need.

[Bertrand Drouhard]

I may be a bit late, but this is a query to find what are the different space categories and on which space they apply :

SELECT l.NAME, l.labelID, cl.CONTENTID, s.SPACENAME, s.SPACEKEY
FROM CONTENT_LABEL cl
LEFT JOIN LABEL l ON (l.LABELID = cl.LABELID)
LEFT JOIN CONTENT c ON (c.CONTENTID = cl.CONTENTID)
LEFT JOIN SPACES s ON (s.SPACEID = c.SPACEID)
WHERE l.NAMESPACE = 'team'

and if you want to find all the spaces that are in the "test" category, just add

AND l.NAME = 'test'