nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL query to get all the issues from a JIRA project

Kishore_l0q82n

Need a SQL query to get all the issues with all the fields (custom fields, system fields (labels)) that are associated with a project for single project.

Accepted answers

Akmal Harith

Hi Kishore,

There's this KB which provides simple SQL queries to view Jira issues from the database.

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

You should also note the issue data is stored across multiple tables so you would most probably need to query against multiple tables to get all the fields. Two useful queries that I can suggest:

Query against the jiraissue table for the issues from project KEY. This table includes the default fields such as summary, description, priority, resolution, issuestatus, security in the form of their respective IDs. You will need to query against their tables respectively to get their values.
```
select * from jiraissue, project
where project.id = jiraissue.project
and project.pkey = 'KEY'
```

Query against the following tables to get custom field values of the issues in the project KEY.

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<br>and jp.pkey = 'KEY'
order by jp.pkey, ji.issuenum

I'd suggest taking a look at Jira's Database schema to understand in detail how data is stored, which may help you moving forward.

Best regards,
Akmal Harith | Atlassian Support

All comments

Jack Nolddor [Sweet Bananas]

Do you mean JQL? If so, you can use Advanced Searching to fetch al issues under a project like this:

project = "My Awesome Project"

Additionally, you can configure if results should be shown as a list or not and which fields should be displayed using the right icon within that page.

Regards

unknown

No, from the database we are trying to pull the results. We are using the PostgreSQL DB.

I want to pull the results with the column names like Jira key, Customfield_1, Summary, description, labels, Customfield_2 & fixed date.

Akmal Harith

Hi Kishore,

There's this KB which provides simple SQL queries to view Jira issues from the database.

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

You should also note the issue data is stored across multiple tables so you would most probably need to query against multiple tables to get all the fields. Two useful queries that I can suggest:

Query against the jiraissue table for the issues from project KEY. This table includes the default fields such as summary, description, priority, resolution, issuestatus, security in the form of their respective IDs. You will need to query against their tables respectively to get their values.
```
select * from jiraissue, project
where project.id = jiraissue.project
and project.pkey = 'KEY'
```

Query against the following tables to get custom field values of the issues in the project KEY.

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<br>and jp.pkey = 'KEY'
order by jp.pkey, ji.issuenum

I'd suggest taking a look at Jira's Database schema to understand in detail how data is stored, which may help you moving forward.

Best regards,
Akmal Harith | Atlassian Support

Kishore_l0q82n

Hi Akmal,

Thank you for providing the information. Now I'm able to pull the field values for specific project by using the below query.


select jp.PKey || '-' || ji.IssueNum as IssueKey, ji.Summary, ji.Description, cf.cfname as Customfield, cfv.stringvalue as Value, l.label, pv.vname as FixVersion
from project jp, jiraissue ji, customfield cf, customfieldvalue cfv, label l, projectversion pv
where jp.id = ji.project
and cfv.issue = ji.id
and cf.id = cfv.customfield
and ji.id = l.issue
and jp.id = pv.project
and jp.PKey = 'KEY'
and cf.cfname = 'Custom field name'
order by jp.pkey, ji.issuenum

Now am facing any issue that when if any one of the field value is empty then the above query is not working. Let me know how can I get all issues from a project even though if the values is present or not for the fields.

Regards,
Kishore Kumar.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-software

jira-software-server

atl-gruland

data-center