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; } } }

DB query to get Issues in EPIC

Santosh V

I need help on DB Query to get list of issues which is linked to EPIC in JIRA application.

Below is the list of issues which is linked in EPIC.

Epic Name is "My Epic"

In Above screenshot, there are 2 issues has been linked to Epic. Need help to get same data in SQL query

Regards

Santosh

Accepted answers

Santosh V

Finally i found it. Below the query which gives list of issues which is linked to Epic

SELECT
li.summary as ISSUES_IN_EPIC, I.ID AS ISSUELINKID
FROM
JIRAISSUE I
INNER JOIN ISSUELINK IL
ON IL.SOURCE = I.ID
INNER JOIN ISSUELINKTYPE ILT
ON ILT.ID = IL.LINKTYPE
INNER JOIN JIRAISSUE LI
ON LI.ID = IL.DESTINATION
INNER JOIN PROJECT PJ
ON PJ.ID = I.PROJECT
INNER JOIN ISSUETYPE IT
ON IT.ID = I.ISSUETYPE
where IT.PNAME = 'Epic'

All comments

Tarun Sapra

Hello @Santosh V

Why do you want to do a DB query?

You can do the query in Jira native JQL and export the results in the CSV/Excel

Tarun Sapra

"Epic Link" = <key of epic>

Above is based on out of the box features

And also possible using JQL functions from paid plugins like SR

https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_issuesinepics

Santosh V

I need DB query (PostgreSQL) because am building dashboard report in power BI.

so it would be great to provide db query

Regards

Santosh

Tarun Sapra

Hello @Santosh V

As per this blog - https://powerbi.microsoft.com/en-us/blog/explore-your-jira-data-with-power-bi/

I don't think it's required to write SQL queries yourself to extract the data from Jira and display via power BI

Santosh V

Finally i found it. Below the query which gives list of issues which is linked to Epic

Filipi Lima

I did a bit of tweaking on what @Santosh V provided and I think it could be useful for others.

To get only issues linked using the "Epic-Story Link" link type, look for the ID on issuelinktype table and replace the 10101 I have at the very end.

It was tested on postgres so you might need to tweak how to concat fields depending on your DB product.

A bit of a glossary for the alias:

PJP: project of the parent
JIP: jiraissue for the parent
PJC: project of the child
JIC: jiraissue for the child

SELECT JIC.summary as child_summary,
concat(concat(PJP.pkey,'-'),JIP.issuenum) as parent_key, JIP.ID as parent_issue_id,
concat(concat(PJC.pkey,'-'),JIC.issuenum) as child_key, JIC.ID as child_issue_id,
ILT.linkname
FROM
JIRAISSUE JIP
INNER JOIN ISSUELINK IL ON IL.SOURCE = JIP.ID
INNER JOIN ISSUELINKTYPE ILT ON ILT.ID = IL.LINKTYPE
INNER JOIN JIRAISSUE JIC ON JIC.ID = IL.DESTINATION
INNER JOIN PROJECT PJP ON PJP.ID = JIP.PROJECT
INNER JOIN PROJECT PJC ON PJC.ID = JIC.PROJECT
INNER JOIN ISSUETYPE IT ON IT.ID = JIP.ISSUETYPE
where IT.PNAME = 'Epic'; --and ILT.id = 10101;

Welcome!

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

Quick Links

Find more posts tagged with

jira

developer