How to find or view an Issue from the database with SQL queries in Jira
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
Please note the following information is provided as-is. Atlassian Support cannot provide further assistance with the SQL queries described below
目次
要約
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).
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).
課題を見る
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;カスタムフィールドを表示する
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;その他の情報
All tables indicated here are seen in Database Schema, from Atlassian developer site. You can also check the: