How to list all projects in a specific Project Category
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
To list all projects that are configured under a specific Project Category through a SQL query to the database.
Solution
Run the following query:
1
2
3
4
5
6
select distinct p.pname, p.pkey
from project p join nodeassociation n
on p.id=n.source_node_id
where n.sink_node_entity='ProjectCategory' and
n.sink_node_id=(select id from projectcategory where cname='Test')
order by p.pname; Replace 'Test' with the project category name that you wish to list.
You may run an alternative query to fetch all projects with their Category:
1
2
3
4
select p.pname, p.pkey, pc.cname from project p
left join nodeassociation na on p.id=na.source_node_id and na.sink_node_entity='ProjectCategory'
left join projectcategory pc on na.sink_node_id = pc.id
group by p.pname, p.pkey, pc.cname;ℹ️ This is tested using PostgreSQL. Please modify it according to the needs of your database.
Was this helpful?