how to get 'epic name' through SQL Query

i would like to have a SQL query wherein I can get the epic name of a story.

The database architecture 'n schema appears to be more confusing.This doesn't even describe much of details (https://developer.atlassian.com/jiradev/jira-platform/jira-architecture/database-schema#Databaseschema-Customfields)

Likewise - I do have tables customfield and customfieldvalue from where I can have the required 'stringvalue' as epic name but id and issue column makes more confusion as customfieldvalue.id is from nowhere to generate?

2 answers

1 accepted

Run this query and replace the 10111 value to the relevant custom field value of your JIRA instance:

select p.pkey, ji.issuenum, cfv.STRINGVALUE
from jiraissue ji join project p on ji.PROJECT = p.ID 
join customfieldvalue cfv on ji.ID = cfv.ISSUE 
where cfv.CUSTOMFIELD = 10111;

Output will contain the issue key and the custom field value.

@Noam Dahan thanks that works - but it returns epic issuetype list. What if I want to pass a pkey and issuenum=1836 (1836 will be key for story issuetype)?

 

select p.pkey, ji.issuenum, cfv.STRINGVALUE,ist.pname
from jiraissue ji join project p on ji.PROJECT = p.ID
join customfieldvalue cfv on ji.ID = cfv.ISSUE
join issuetype ist on ji.issuetype = ist.id
where cfv.CUSTOMFIELD = 10004 and p.pkey='ATPCWS' and ji.issuenum=1836

Yes, the output columns will show: 
pkey = the project key of relevant issue
issuenum = the number of the relevant issue
stringvalue = the custom filed value for that issue

@Noam Dahan,

Query you provided worked; but output was only with EPIC issue type - wherein I was looking for a STORY ID to be passed and get the epic link 'name' (thats stringvalue). Your code really helped

I ended up creating the below one:

select ji.issuenum AS Epic_Key, cfv.STRINGVALUE AS Epic_Name
from jiraissue ji
join customfieldvalue cfv on ji.ID = cfv.ISSUE 
join issuetype ist on ji.issuetype = ist.id 
where cfv.CUSTOMFIELD = 10004 
and ji.issuenum=(
select issuenum from jiraissue where id=(
select islk.source
from 
jiraissue ji join issuelink islk on ji.id=islk.source
join issuelinktype ilt on islk.linktype=ilt.id
join jiraissue c on islk.destination = c.id
where
ilt.linkname='Epic-Story Link' and c.id=(select jre.id from jiraissue jre, project prj where jre.project=prj.id and prj.pkey='ATPCWS' and jre.issuenum=1836))
)

Glad to help. Cheers!

How do i get the epic issuetype  jira issues where the epic_name is empty. I have case like this. Any help is appreciated.

Peyyala,

SELECT p.pkey || '-' || ji.issuenum AS issueid,cfv.stringvalue AS epicname
FROM jiraissue ji
LEFT JOIN project p ON p.id=ji.project
LEFT JOIN customfieldvalue cfv ON cfv.customfield=(SELECT id FROM customfield
    WHERE cfname LIKE 'Epic Name') AND cfv.issue=ji.id
WHERE ji.issuetype=(SELECT id FROM issuetype WHERE pname LIKE 'Epic') AND (cfv.stringvalue IS NULL OR cfv.stringvalue = '')
ORDER BY p.pkey, ji.issuenum ;

Although this should never happen as the Epic Name field is required.

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Marketplace Apps

Tips on how to choose the best estimation method for your planning

Planning and grooming sessions all come with their own sets of rules. Team members meet to estimate stories or other work items, all according to an agreed-upon process. And with every session comes ...

65 views 0 11
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you