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

How do I make SQL queries in JIRA?

I am new to developing in JIRA and do not know where to start on making SQL queries to the database in JIRA. I'm trying to query all Issues that have a particular custom field, and then see if each of those issues contains a number of different strings for those Issues that do have the custom field. Can anyone point me in the right direction? 

4 answers

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

3 votes

Really, don't bother.  The JIRA database is built for data storage, not reporting or querying. 

As an example, take a simple custom field - the SQL for answering "What components are on ABC-123" involves joining at least six tables together.  Or for "what colour are the issues in that project? (colour being a custom field)" - you're looking at 7 to 10 depending on the type of field.

The database is abstracted from the application and it's far better to ignore it and use the API to make queries, especially as the API provides access to JQL - that gives you a list of issues, and then you can run that through a display of some sort.

Chris Fuller Atlassian Team May 05, 2016

+1.

Add to this the fact that the Java API Policy for JIRA does not specify anything at all about the datamodel.  We do not consider the database layout to be part of the API, and even bugfix releases could break anything that you did.

When you're talking about a tiny custom plugin whose original author is still with the company, this might be something you can live with, but such things tend to grow out of control and become fragile over time.  Life is easier for all of us when you stick with what we formally support.

+1.

All issue data is indexed using Apache Lucene so a JQL search will be much faster than querying the database.

1 vote
Joe Pitt Community Leader May 05, 2016

Just don't update the database outside of JIRA or the API. You'll break JIRA

Yeah I've come across that a few places but still haven't been able to find a likely quite basic explanation of how to get started with SQL queries in JIRA, do you know of any tutorials or resources?

1 vote
Chris Fuller Atlassian Team May 05, 2016

Honestly, from what you've expressed in this question, searching with JQL would seem to make a lot more sense. Here are a couple write-ups about it:

And you can run JQL from code, as well:

An example that I think is similar to what you were asking for might be this query on jira.atlassian.com:

resolution IS EMPTY AND "Bugmaster Rank" IS NOT EMPTY AND (text ~ "test" OR text ~ "hello") ORDER BY key

 

https://jira.atlassian.com/issues?jql=resolution IS EMPTY AND "Bugmaster Rank" IS NOT EMPTY AND (text ~ "test" OR text ~ "hello") ORDER BY key

 

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

Community showcase
Published in Jira

Jira Cloud for Google Sheets: Automatically Refresh Your Data!

Remember that time you realized it was possible to refresh your Jira data in Google sheets with just one click? What if we told you that you can now get the latest data with no clicks at all?! Zero! ...

526 views 3 12
Read article

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