How to find or view an Issue from the database with SQL queries in Jira
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
Please note the following information is provided as-is. Atlassian Support cannot provide further assistance with the SQL queries described below
Table of contents
This page shows how to find Jira issues along with some common details about them in the database.
These queries were written for PostgreSQL for other database types the concat function needs to be adjusted;
PostgreSQL, SQLite, and Oracle:
(jp.pkey || '-' || ji.issuenum)MySQL:
CONCAT(jp.pkey, '-', ji.issuenum)SQL Server:
(jp.pkey + '-' + ji.issuenum)
For further reference in the 'jiraissue' table, you may consult our Database Issue fields documentation from Atlassian Developer website.
All SQL queries below are configured to return the details for a specific issue through the WHERE CONCAT clause, where 'IssueKey' must be replaced by the actual issue Key you re searching for.
Here's a breakdown of how the Issue Key is constructed using the 'jiraissue' and 'project' tables.
Project Table - pkey: The Key given to a Jira Project. (Example: On PRJ-1234, 'PRJ' is the pkey).
JiraIssue Table - issuenum: The numeric portion of an issue Key. (Example: On PRJ-1234, '1234' is the issuenum).
đź’ˇIf you so wish, you may remove this portion to return all issues in the instance or adjust it as per your needs (such as returning all issues from a given project).
Solution
Viewing an Issue
The main details of an issue along with its native fields of issues are found in the 'jiraissue' table.
1
2
3
4
SELECT (jp.pkey || '-' || ji.issuenum) AS issuekey, ji.*
FROM project jp
JOIN jiraissue ji ON jp.id = ji.project
WHERE (jp.pkey || '-' || ji.issuenum) = 'IssueKey';Viewing issue versions
1
2
3
4
5
6
7
8
SELECT (jp.pkey || '-' || ji.issuenum) AS issuekey, pv.vname
FROM project jp
JOIN jiraissue ji ON jp.id = ji.project
JOIN nodeassociation na ON na.source_node_id = ji.id
JOIN projectversion pv ON pv.id = na.sink_node_id
WHERE na.sink_node_entity = 'Version'
AND (jp.pkey || '-' || ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum;Viewing issue components
1
2
3
4
5
6
7
8
9
SELECT (jp.pkey || '-' || ji.issuenum) AS issuekey, c.cname
FROM project jp
JOIN jiraissue ji ON jp.id = ji.project
JOIN nodeassociation na ON na.source_node_id = ji.id
JOIN component c ON c.id = na.sink_node_id
WHERE na.source_node_entity = 'Issue'
AND na.sink_node_entity = 'Component'
AND (jp.pkey || '-' || ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum, c.cname;Viewing custom fields
1
2
3
4
5
6
7
SELECT (jp.pkey || '-' || ji.issuenum) AS issuekey, cf.cfname, cfv.stringvalue, cfv.numbervalue, cfv.textvalue, cfv.datevalue, cfv.valuetype
FROM project jp
JOIN jiraissue ji ON jp.id = ji.project
JOIN customfieldvalue cfv ON cfv.issue = ji.id
JOIN customfield cf ON cf.id = cfv.customfield
WHERE (jp.pkey || '-' || ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum, cf.cfname;Viewing Request participants
1
2
3
4
5
6
7
8
9
SELECT (jp.pkey || '-' || ji.issuenum) AS issuekey, cf.cfname, cfv.stringvalue, au.lower_user_name
FROM project jp
JOIN jiraissue ji ON jp.id = ji.project
JOIN customfieldvalue cfv ON cfv.issue = ji.id
JOIN customfield cf ON cf.id = cfv.customfield
JOIN app_user au ON cfv.stringvalue = au.user_key
WHERE cf.cfname ='Request participants'
AND (jp.pkey || '-' || ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum;Viewing Approvers
1
2
3
4
5
6
7
8
9
SELECT (jp.pkey || '-' || ji.issuenum) AS issuekey, cf.cfname, cfv.stringvalue, au.lower_user_name
FROM project jp
JOIN jiraissue ji ON jp.id = ji.project
JOIN customfieldvalue cfv ON cfv.issue = ji.id
JOIN customfield cf ON cf.id = cfv.customfield
JOIN app_user au ON cfv.stringvalue = au.user_key
WHERE cf.cfname ='Approvers'
AND (jp.pkey || '-' || ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum;Viewing Watchers
1
2
3
4
5
6
7
SELECT (jp.pkey || '-' || ji.issuenum) AS issuekey, ua.source_name, au.lower_user_name
FROM project jp
JOIN jiraissue ji ON jp.id = ji.project
JOIN userassociation ua ON ua.sink_node_id= ji.id
JOIN app_user au ON ua.source_name = au.user_key
WHERE (jp.pkey || '-' || ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum;Other information
All tables indicated here are seen in Database Schema, from Atlassian developer site. You can also check the:
Was this helpful?