Becoming a JIRA Search Ninja - Advanced searches

1488465441776-8820e73c-c3fb-4242-a52a-5ff125b525fa_ (1).jpg

JIRA is a cool tool but it becomes even cooler if you know how to leverage it's powerful search features! Some of you probably already discovered the powerful JIRA Query Language (JQL) and that's what this series of blogposts is all about. In the next few posts we'll cover:

Advanced searches in JIRA (using JQL)

The next step towards becoming a ninja: JQL

The basic search is nice and it allows you to easily create basic filters which usually cover 90% of the cases but what if you want to dig a little deeper? For example: you'd like to get all issues that does not have a Description.

That's currently not possible with the basic search feature in JIRA, to achieve this you'll have to use JQL, JIRA Query Language. To switch the JIRA search to Advance mode you'll simply click the Advanced link next to the Basic search form.

SwitchToAdvanced.gifAs you can see it'll automatically convert the Basic filter into JQL.

SQL or JQL, what's the difference?

Maybe you've already noticed but JQL looks very similar to SQL, so if you're familiar with SQL, JQL will be a breeze. The simplest way to get going is to start typing with the following syntax:

 

<field_name> = <value>

JIRA will try to help you by providing an autocomplete for most of the fields and even values:

 

JQLAutoComplete.gif

While typing, JIRA will show you whether the query is valid image2017-3-8 11-36-42.pngor not image2017-3-8 11-37-6.png, when you hover it'll tell you which line and character the error can be found image2017-3-8 11-37-58.png

But when I press enter it executes the query instead of adding a new line to my query?

Yes exactly, you should do Shift + Enter to create a new line in your query. This will make it easier for you to debug syntax errors as JIRA will tell you on which line the error can be found.

Sort the results

Just like in most tools you can sort the results on a certain column. In JQL this can easily be achieved by using ORDER BY <column>. For example:

project = Collaboration ORDER BY created

This will give you a list of all issues in the Collaboration project sorted on the date they were created (most recent first).

How do I reverse sort?

By default, JIRA will sort descending (from large to small) if you don't specify anything. If you would like to control the order in which the sort happens, you can specify that right after the column name:

project = Collaboration ORDER BY created ASC

Sort on multiple fields?

You can also sort on multiple fields to cover the cases where the value of a certain column is the same for multiple issues.

project = Collaboration ORDER BY created ASC, updated DESC

This query will sort the issues based on the creation date (oldest first) and when the creation date is the same for two issues it'll sort these issues on the updated date (newest first).

JQL Reference

Atlassian has created a JQL reference where you can find all keywords, operators, etc that can be used in JQL. This is your go-to-guide when you want to discover new search capabilities. But to get you started some examples:

Keywords

AND

project = Collaboration AND status = "In Progress"

This will only return issues that match both clauses (are part of the Collaboration project and have their status set to "In Progress")

OR

project = Collaboration OR status = "In Progress"

Returns all issues from either the Collaboration project or that have their status set to "In Progress".

IS

description IS EMPTY

This will return all issues that have no description.

Operators

!=

status != "To Do"

Returns all issues except for those that have their status set to "To Do"

>=

"Story Points" >= 5

Find all issues that have Story Points which are greater than or equal a given value

Similar

  • > | greater than
  • < | smaller than
  • <= | smaller than or equal

IN

status IN ("To Do", "In Progress", "Closed")

is the same as

status = "To Do" OR status = "In Progress" OR status = "Closed"

Find all issues which are either have the status "To Do", "In Progress" or "Closed"

Reverse

  • NOT IN

Questions/Problems?
Please use the comments below.

3 comments

I am trying to limit the fields that are returned from a JQL query and when using a URL in a rest call.

What is the syntax of the expression I need to add to the end of a query to limit the fields returned?

status = Accepted & fields = key , ID  is one of many attempts that have failed. 

Thanks,

Maarten Cautreels Community Champion Jun 02, 2017

Hi Tom,

The following GET request works for me:

/rest/api/latest/search?jql=project=ISD&fields=key,summary

This only returns the key and summary of all issues matching the JQL provided. The ID itself is always included.

Best,

Maarten

Great tips!!! A couple more handy ones we use if you are looking at simplifying statuses and/or have to include a current sprint and is very handy to also use in Confluence if you need to keep other teams up to date on your efforts:

  • All open work: Status != "Done" or Status != "Complete"
  • Current work: Sprint in openSprints()

Because we work in so many varieties of topics and have to update so many teams across the company, we use the Insert JIRA Issue/Filter macro in Confluence based on search strings from JIRA:

project = ABC AND Labels = "ProductName" AND Status != "Done"

We then pick minimal fields to display as based upon the audience, very similar to Maarten's GET request.

Comment

Log in or Sign up to comment
Community showcase
Posted Sep 18, 2018 in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

27,224 views 2 7
Join discussion

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