How to find issues with a specific Fix Version/Affected Version using database query
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Purpose
How to query the database to identify all issues which have a particular Fix Version/Affected Version
Solution
To find all issues of a particular "Fix Version", you will have to join several tables in the database.
Table Name | Usage |
---|---|
jiraissue | Stores the id of a particular issue |
projectversion | Stores the name and id of a Fix Version |
nodeassociation | Stores the link between id of an issue to the version it's associated to |
The columns in the nodeassociation table are important to link the issues to the fixversion. Here's a brief explanation
Column | Usage |
---|---|
source_node_id | Stores the id of the issue from the jiraissue table |
source_node_entity | Stores the level of the association (eg. issue, project) |
sink_node_id | Stores the id of the version from the projectversion table |
sink_node_entity | Stores the type of association (eg. Version, Workflow Scheme, Permissio Scheme, etc) |
association_type | Stores the more detailed type of association depending on the value of sink_node_entity (eg. IssueVersion, IssueFixVersion, ProjectScheme) |
This is a simple example for the tables with the relevant columns
project
id | pname | pkey |
---|---|---|
10000 | Project A | PROA |
jiraissue
id | project | issuenum |
---|---|---|
22500 | 10000 | 1 |
22600 | 10000 | 2 |
projectversion
id | vname |
---|---|
30000 | version1 |
30001 | version2 |
nodeassociation
source_node_id | source_node_entity | sink_node_id | sink_node_entity | association_type |
---|---|---|---|---|
22500 | Issue | 30000 | Version | IssueFixVersion |
22500 | Issue | 30000 | Version | IssueVersion |
22600 | Issue | 30001 | Version | IssueFixVersion |
The above example means the following
Issue Key | Affected Version | Fix Version |
---|---|---|
PROA-1 | version1 | version1 |
PROA-2 | version2 |
This being said, you will have to join several tables to find the information you are looking for. For example, this is a query which will retrieve all issues which have a specific FixVersion.
Please make sure to replace <ADD_HERE_THE_VERSION_NAME> with the version you are interested in before running the SQL query.
This query was written and tested using a PostgreSQL database, so changes might have to be made for other database
SELECT
IT.pname AS iType
, P.pkey||'-'||JI.issuenum AS Key
, JI.summary AS Summary
, NA.association_type AS aType
, PV.vname AS Version
FROM
jiraissue JI
INNER JOIN project P ON P.id=JI.project
INNER JOIN issuetype IT ON IT.id = JI.issuetype
INNER JOIN nodeassociation NA ON NA.source_node_id=JI.id AND NA.source_node_entity='Issue'
INNER JOIN projectversion PV ON PV.ID=NA.sink_node_id AND NA.sink_node_entity='Version'
WHERE PV.vname = '<ADD_HERE_THE_VERSION_NAME>';