How to Query Jira Issues by Fix Version Directly from the 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
How to query the database to identify all issues that have a particular Fix 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 the ID of an issue to the version it's associated with |
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 (for example, issue, project) |
sink_node_id | Stores the ID of the version from the projectversion table |
sink_node_entity | Stores the type of association (for example, Version, Workflow Scheme, Permission Scheme, etc.) |
association_type | Stores the more detailed type of association depending on the value of sink_node_entity (for example, 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>';Was this helpful?