Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

SQL query that is similar to view issues page Edited

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!

jira issues page.JPG

2 answers

0 votes

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.

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?

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!

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?

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you