To find or view a Jira issue from the database using SQL

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.

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


Summary

This page shows how to find Jira issues in Database via SQL.

As main reference, the jiraissue table is very well documented in Database Issue fields from Atlassian Developer web site.

Viewing an Issue

The main information of the issues are found in the table jiraissue, which can be joined to other tables for a more human readable data.

To view issues from a given project you might consider the SQL:

select * from jiraissue where project = (select id from project where pkey='JRA');

Where pkey, or project key is the most memorable and recognizable piece of an issue's identifier (e.g issue PRJ-1234 has project key PRJ and issue number 1234).


Viewing issue versions

select jp.pkey, ji.issuenum, pv.vname
from project jp, jiraissue ji, projectversion pv, nodeassociation na
where jp.id = ji.project
and na.sink_node_entity = 'Version'
and na.source_node_id = ji.id
and pv.id = na.sink_node_id
order by jp.pkey, ji.issuenum


Viewing issue components

select jp.pkey, ji.issuenum, c.cname
from project jp, jiraissue ji, component c, nodeassociation na
where jp.id = ji.project
and na.source_node_entity = 'Issue'
and na.sink_node_entity = 'Component'
and na.source_node_id = ji.id
and c.id = na.sink_node_id


Viewing custom fields

select jp.pkey, ji.issuenum, cf.cfname, cfv.stringvalue, cfv.numbervalue
from project jp, jiraissue ji, customfield cf, customfieldvalue cfv
where jp.id = ji.project
and cfv.issue = ji.id
and cf.id = cfv.customfield
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 3, 2022

Was this helpful?

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