Tag Archives: jql

Top Ten -> Twelve JQL Tips to Improve Your JIRA Searches

Jira ate my brain

JQL – it’s not just for programmers.

Somehow I keep ending up at companies that use Jira and Jira Agile for tracking their teams’ work,  I’ve been using Jira and Confluence a long time – Confluence since it came out in 2004  and didn’t have a rich text editor – and so no matter what my official role, I end up doing a lot of troubleshooting and help for folks using these tools.

So today I thought I’d share some tips about querying with Jira.  If you use Jira, you know that it supports a basic dropdown and picker-driven query UI like this:

Jira basic search

This is great as a starting point, but to really get to the good stuff you’ll want to click Advanced and start typing Jira Query Language (JQL) instead.  It’s modeled on SQL (Structured Query Language) for databases, but don’t be intimidated – JQL doesn’t go much beyond the equivalent of SELECTing things WHERE certain criteria apply.  For example:

Go ahead, give it a try!  Advanced mode has auto-suggest, so just start typing a field name and see what happens.

Background Reading

The tips below are some good secret sauce drawn from common questions I get, but if you want a more general introduction, head over to these places:

Ok, on to the tips.

Top Ten Twelve JQL Tips

1. Learn JQL by Switching Between Basic and Advanced Mode

Learn by doing! Use Basic mode to build a query then click Advanced to see the JQL JIRA created to run your query. By working back and forth between the two modes you can figure out quite a bit. You’ll know you’re getting good when you write JQL, click Basic, and get the message “this query is too complex to display in Basic mode”.

2. You Can Query The Past

JIRA supports query operators like WAS for some fields. For example you can write stuff like:

to find all the issues John Price started progress on before 2018. Cool!

3. Saved JQL Queries Power Dashboards

Dashboards are a whole other topic, but making dashboards for yourself or others basically consists of:

  • Writing a JQL query and saving it.
  • Creating a dashboard.
  • Adding charts and other gadgets and telling them to use your saved query as the data source.

Don’t forget to have someone test your dashboard to be sure you’ve properly shared it and its queries.

4. Email Me Those Query Results!

This isn’t strictly a JQL tip, but you should be aware that you can Subscribe to any saved filter and have JIRA email you a list of matching issues weekly, daily, or more often. This is a great way to be reminded to go check JIRA for things like:

  • all the new stories created on the backlog in the last day (these need to be prioritized)
  • all the support requests that no one has picked up yet
  • issues missing important data (no Component, Product, etc.).

You can also use subscriptions in conjunction with a less chatty Notification Scheme (ask an admin) to cut down on the dreaded Jira spam.

Check out Receiving Search Results Via Email in the official JIRA documentation.

5. Query For Empty Resolution To Find All Open Statuses

If you want to find issues in any open status (New, In Progress, In Test, etc.) it’s a pain to list all the statuses in your query. When an issue gets Closed or set to a done-equivalent status like Awaiting Deployment, a second status-related field called Resolution gets set to Done by default, or to other values like Won’t Fix if your workflow lets you manually choose it. Also, Resolution gets cleared automatically when you reopen an issue. That means that:


is shorthand for something like

and way easier to type.

6. Search All Text Fields At Once

For free text fields you use the tilde (~) instead of equals (=) to match content. It basically means “contains”, like this:

But sometimes you aren’t sure if the text you want is in Summary, Description, Acceptance Criteria, or somewhere else. So you can just type:

and JIRA will return matches for all those fields plus any other text fields.

7. Search Projects By Key, Not Name

If you want to limit a search to a project, either the name or the key will work. The project key is the alphabetic part of the issue IDs in that project, like CATS for CATS-123. So you could save queries like this:

But if the team changes the project name to “The Cool Cats” all your saved queries will stop working. JIRA auto-suggests the names, which doesn’t help you have good habits in this case. Instead you should always use:

so the queries keep working if the name changes. As a bonus, this keeps working even if the key changes since JIRA keeps track of old project keys. The Tooth and Paw team became Cool Cats a while ago, but you can still type

and it will give you a list of Cool Cats issues. Like magic.

8. Functions Are Your Friend

Most simple queries look like this:

This one gives all the bugs I’m working on right now and is just a list of “this field has this value and that field has that value” statements. But JIRA has lots of built-in functions to simplify your life and you can drop these right into your queries in a straightforward way, like this:

This shows me all the open items assigned to me that are in past sprints. There shouldn’t be any … but I like to check now and then to keep JIRA neat. Better yet, because I said currentUser() instead of “john.price” I could save this as “My Old Open Issues” and share it, and each person running it would see their own list of old open issues. Maybe I’ll even get my team to Subscribe to the filter (tip #4) and they’ll get an email every Monday to remind them to clean up old issues.

9. Parentheses Matter

Just like in SQL, you can use parentheses to tell JIRA how to interpret what you want. Let’s say I want all the CATS project issues assigned to either me or Felix Cat. I can write

but JIRA thinks I mean “CATS items assigned to Felix, or any item assigned to me”. I can tell something’s wrong because if I swap the order of names I get a different number of results. So I use parentheses:

which JIRA reads as “in the CATS project and assigned to one of these two people”. By the way, in this particular case I could have just done this:

but I wanted to show how parentheses work.

10. Know Your Dates

Writing useful JIRA queries often means restricting results to a date range. How many bugs were found during Program Increment 2? How many open issues haven’t been updated in over a year? Where are those issues I created today?

There are three ways to specify dates in queries:

Use the actual date, like:

This returns everything created after the start of 2017.

Specify a time relative to today, like:

This gives you everything created within the last 7 days. The results change as you run the query over time.

Use some of the built-in date functions, like this:

This works just like the first example but will stay useful if we run it next year. You can also give a number to the various startOf/endOf functions if you care about a day, week, month or year other than the current one, like:

This will always give you issues created during the previous full calendar week (Sunday through Saturday).

11. EMPTY And Not Equal Are Not The Same

You can write IS EMPTY in your queries to ask for issues where the field in question has no value specified. But if you’re not a developer or SQL expert you might be surprised by how this relates to the not equal ( != ) operator. Not Equal means “has a value, but not this one” and doesn’t return issues that have no value at all. Let’s say I want to find bugs in my project that have the wrong product specified. My product is Cat Toy Caddy so I type:

That’s great, but as it turns out there aren’t any with a different product. But there are a bunch where we forgot to put anything at all. Let’s try again:

Ahh … there we go. Note the parentheses.

12. Supercharge Your Text Searches

You may have noticed that JIRA doesn’t always seem to find issues that contain the text you’re searching for. Like:

doesn’t find an issue called “Integrate RealtimeBoard With Confluence”. What gives?

JIRA uses Lucene (a popular search engine) for text searches. Lucene uses something called word stemming instead of doing exact text matching. So searching for “customize” will find “customizing” because they are forms of the same stem, but searching for “cust” finds neither. Luckily you can just do this:

and now it finds your issue. All we did was 1) add an asterisk to the end of the word to tell JIRA to find anything starting with “conf” and 2) put the text in quotes so the asterisk doesn’t create a query syntax error.

For more advanced text searching tips, see https://confluence.atlassian.com/jiracorecloud/search-syntax-for-text-fields-765593720.html.

Just a couple more examples for fun:

Note how in all cases the whole text search including operators like AND and NOT is in quotes.

Whew, That Was a Lot

Hopefully even those of you who have been using JQL for a while found something new.  You should also take a look at some of the popular JQL-related plugins like the JQL Tricks Plugin (Server only) or JQL Search Extensions for Jira (Cloud or Server), which give you even more search options.  

This is just the beginning … have fun!