How to find or view an Issue from the database with SQL queries in Jira

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

Please note the following information is provided as-is. Atlassian Support cannot provide further assistance with the SQL queries described below

Table of contents

Summary

This page shows how to find Jira issues along with some common details about them in the database.

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).

(lightbulb)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).

Viewing an Issue

The main details of an issue along with its native fields of issues are found in the 'jiraissue' table.

SELECT CONCAT(jp.pkey, '-', ji.issuenum) AS issuekey, ji.*
FROM project jp
JOIN jiraissue ji ON jp.id = ji.project
WHERE CONCAT(jp.pkey, '-', ji.issuenum) = 'IssueKey';

Viewing issue versions

SELECT CONCAT(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 CONCAT(jp.pkey, '-', ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum;

Viewing issue components

SELECT CONCAT(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 CONCAT(jp.pkey, '-', ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum, c.cname;

Viewing custom fields

SELECT CONCAT(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 CONCAT(jp.pkey, '-', ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum, cf.cfname;

Viewing Request participants

SELECT CONCAT(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 CONCAT(jp.pkey, '-', ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum;

Viewing Approvers

SELECT CONCAT(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 CONCAT(jp.pkey, '-', ji.issuenum) = 'IssueKey'
ORDER BY jp.pkey, ji.issuenum;

Viewing Watchers

SELECT CONCAT(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 CONCAT(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:





Last modified on May 19, 2023

Was this helpful?

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