SQL query to get issue components from Jira database
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
You may notice that there is a 'component' column in the public.jiraissue table, however, this column is not used (always containing null values) and is not present on the database schema for the latest Jira versions 8+
Environment
Jira 8, 9
Solution
In fact, the list of components is stored in public.component table:
public.component | |
|---|---|
id | numeric(18, 0) not null |
€ project | numeric(18, 0) |
cname | varchar(255) |
description | text |
url | varchar(255) |
lead | varchar(255) |
assigneetype | numeric(18, 0) |
archived | varchar(10) |
deleted | varchar(10) |
Jira links the issue with its components via public.nodeassociation table:
public.nodeassociation | |
|---|---|
source _node_id | numeric(18, 0) not null |
source _node _entity | varchar(60) not null |
sink_node_id | numeric(18, 0) not null |
sink_node_entity | varchar(60) not null |
association _type | varchar(60) not null |
sequence | numeric(9, 0) |
sink_node_entiry = 'Component' defines the component association
sink_node_id refers to component id (from public.component table)
source_node_id is the id of the issue with components
This sample query selects issue summary, key, link, status and components (comma separated) of the issue under specific project (Project key needs to be specified in the below query)
1
2
3
4
5
6
7
select ji.summary, concat(p.pkey, '-', issuenum) AS ISSUEKEY, concat((select PS.propertyvalue from propertyentry PE, propertystring PS where property_key = 'jira.baseurl' and PS.id = PE.id), '/browse/', concat(p.pkey, '-', issuenum)) as LINK, ist.pname as STATUS, string_agg(c.cname, ', ') as COMPONENTS from jiraissue ji
left outer join nodeassociation na on ji.id = na.source_node_id and na.sink_node_entity = 'Component'
left join component c on na.sink_node_id = c.id
join project p on p.id =ji.project
join issuestatus ist on ji.issuestatus = ist.id
where p.pkey = '<PROJECT KEY>'
group by ji.summary, p.pkey, ist.pname, issuenum order by issuenum;Was this helpful?