Becoming a JIRA Search Ninja - Advanced searches

[#1]

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:

• Basic searches in JIRA
• Advanced searches in JIRA (using JQL)
• History searches in JIRA
• The secret powers of JQL

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.

As 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:

While typing, JIRA will show you whether the query is valid or not , when you hover it'll tell you which line and character the error can be found 

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.

[Tom DelGiudice]

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]

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

[David Kornegay]

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.

[Dharmendra Kumar]

is there any way to return the assigned history of a task? Suppose there is a task which was assigned to developer and then after developement, it got assigned to QA and after QA completed, it is assigned to someone else for review. I want to get all tickets which were assigned to a developer. 

Any suggestions?

[Dan Russell]

How can we create a report for product checkout that will show the PCO number, the details, and the amount.  We need the option to export it in Excel.  What I have tried so far only shows how many and what dates they were purchased on: