How to find issues with a specific Fix Version/Affected Version using database query

Still need help?

The Atlassian Community is here for you.

Ask the community

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 NameUsage
jiraissueStores the id of a particular issue
projectversionStores the name and id of a Fix Version
nodeassociationStores 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

ColumnUsage
source_node_idStores the id of the issue from the jiraissue table
source_node_entityStores the level of the association (eg. issue, project)
sink_node_idStores the id of the version from the projectversion table
sink_node_entityStores the type of association (eg. Version, Workflow Scheme, Permissio Scheme, etc)
association_typeStores 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

idpnamepkey
10000Project APROA


jiraissue

idprojectissuenum
22500100001
22600100002


projectversion

idvname
30000version1
30001version2


nodeassociation

source_node_idsource_node_entitysink_node_idsink_node_entityassociation_type
22500Issue30000VersionIssueFixVersion
22500Issue30000VersionIssueVersion
22600Issue30001VersionIssueFixVersion



The above example means the following

Issue KeyAffected VersionFix Version
PROA-1version1version1
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.

(info) Please make sure to replace <ADD_HERE_THE_VERSION_NAME> with the version you are interested in before running the SQL query.
(info) 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>';

Last modified on Jan 8, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.