SQL query that is similar to view issues page

[Vida Makiling]

Hi, we were able to replicate Jira into our data warehouse and I am trying to create a SQL query the produces similar output as in the issues page in Jira - see below. The table design is a bit complicated so I am hoping to get help. Thank you!

[Vida Makiling]

Thank you, Nic. This is really good advice.

I assume we would be replicating the Filter function to return data but this itself has a problem since the Filter is limited to one row per issue. Unless the API allows for more complex logic to enable calls for transactional data, such as Worklogs. Is this the case?

[Nic Brough -Adaptavist-]

I would very strongly recommend you do not try this.  SQL is the single worst way to try to read Jira data, as the database is pretty much the opposite of sane reporting structure.

Your first line of report (on an issue) is pretty minimal but already needs 9 joins, and that's assuming the labels field is empty (that's a 2 deep join for each label you have on it).  You're not even looking at the more complex fields, comments, history, custom fields and history.

You should look to using the REST API instead - for an issue, you can issue one call and get everything back from a simple request.  And in fact for search results in full, still a single call.

[Vida Makiling]

Thank you, Nic! This is really good advice.

I assume we would be replicating the Filter function to return data but this itself has a problem since the Filter is limited to one row per issue. Unless the API allows for more complex logic to enable calls for transactional data, such as Worklogs. Is that the case?

[Nic Brough -Adaptavist-]

A search will return a list of issues that match, and it'll do it in full, you'll actually be wanting to think about limiting what it returns to just the fields you need!