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
Joseph Pitt Community Champion 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?

0 votes
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 Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,885 views 19 22
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