nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How do I make SQL queries in JIRA?

Matt H4

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?

Accepted answers

All comments

Joe Pitt

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

Matt H4

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?

unknown

This might help you get started;

https://confluence.atlassian.com/display/JIRA041/Example+SQL+queries+for+JIRA

Nic Brough -Adaptavist-

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.

crf

+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.

crf

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

Petar Petrov _Appfire_

+1.

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-development

jira-core