JIRA SQL query to get the issue hierarchy level detai;s

[narendra k]

I would like to get below details in JIRA db with SQL queries

Issues under Epic

Issues under Initiative

Stories under Epics

sub-tasks under Issues.

I looked into previous discussions on these, but there is some confusion like some people used issuetype, some used issuelinktype and some used customfield. Can anyone please point me to the right direction to get above details

[Tarun Sapra]

Hello @narendra k

Jira DB schema is pretty complicated one, and what you want is available via JQL query which can then be displayed on the Dashboard gadget or exported via CSV, thus is there any specific reason you want to do it via DB calls.

[narendra k]

Thanks for the reply @Tarun Sapra!

We want to generate some Dashboards on JIRA db. I am not sure how to use JQL here. However, my requirement is to use JIRA DB to generate reports.

[Tarun Sapra]

Jira Dashboards are made of Gadgets and Gadgets display issues fetched by JQL filters. Thus, you can use  JQl filter along with some plugins to create insightful dashboards

https://confluence.atlassian.com/jirasoftwareserver077/configuring-dashboards-945534745.html

https://moduscreate.com/blog/jira-how-to-create-a-dashboard/

https://www.smartsheet.com/dashboards-view-creating-best-jira-dashboard-you-and-your-team

[narendra k]

That's Nice!

But unfortunately, I have to use JIRA db. Is it difficult to create dashboards on JIRA db??

[Nic Brough -Adaptavist-]

Please, don't use the database.  It's not intended for reporting, so you'll be making some torturous and complex SQL for even the most simple of queries.

There are no functions to expose any of the raw database tables anywhere in the UI, including the dashboards.

I also notice you mention "cloud" in the tags, which makes this even more simple - you have no access to the database and will not get it.

[narendra k]

Yes, Nic. You are correct. The data retrieval is very slow even for small queries. Here my goal is to build reports on jira data joining with my organization employee data. I believe dashboards using JQL won't work here to build reports in JIRA itself as I need to join with my organization data tables. So, I need to use jira db for this purpose. 

I am thinking that migrate the JIRA db to my local SQL server and build reports on that. Is it work for me?

[Arkadiusz Głowacki]

Regardless whether want to build for server or cloud, instead of using DB directly you should use provided by Atlassian API. For example, to find sub-tasks under issue ABC-1 you can call search REST endpoint, with JQL like:

parent = ABC-1

this will return JSON with issues that are sub-tasks of ABC-1,

 to find stories under epic ABC-1 you can call a search REST endpoint with JQL like:

"Epic Link" = ABC-1 and issuetype = story

This way you can get the data you are interested in without relying on the database structure which can be changed by Atlassian any time and without warning. The API will mostly remain unchanged, even between major Jira releases. Also going directly to the database means that you need to take care of permissions by yourself and this will be very hard to implement, by using Jira API the results you present will already take permissions into account.