In this article, I propose to start exploring the advanced search functions offered by Jira. Advanced search, or JQL, stands for Jira Query Language. It looks a bit like SQL in terms of syntax.
How to switch to advanced search in Jira
To display the advanced search window, click on the "Advanced Search" link. It is located at the top right of the search window if you are in basic mode.
When you activate this mode, the active search is translated into JQL. Good news, the advanced search has auto-completion. This means that when you are writing, the search suggests words you can use. Each clause of the search consists of a criterion which is mostly a Jira field, an operator (=, >, !=, etc.) and a comparison value.
However, when you start, the search box is empty. If you are intimidated by this, I suggest you start your search structure in basic mode.
An example of advanced search in Jira: search for dates
Let's say you want to search for applications created more than 30 days ago. First, open the basic search and choose the "Date Created" field. In the selection box, check "More than 30 days ago". Then switch to advanced mode to see the entry in JQL.
You see, the query is :
created <= -30d
This means "all requests for which the creation date is less than time T minus 30 days".
If you wanted to submit a list of all requests expected in the next 7 days, the query would be :
due < 7d
This means "all requests for which the due date is less than time T plus 7 days".
This notation is not very intuitive. Going through the basic search to create the draft query simplifies the operation.
Now that we know how to perform an advanced search, let's take a look at the examples of searches that cannot be performed in basic mode or that are difficult to perform.
How to exclude a project in a search?
For this first example, I'm going to start by doing a basic search. First, I do a search on the project and I choose the one I want to exclude, for example, the IOS project. Then I switch to the advanced search and replace my "=" operator with "!=". This is the beginner mode. In advanced mode, we will write the query directly. So I delete, I start again, and you will see the autocompletion directly.
Project != IOS
I select the criterion "Project", the operator "different" in the list that is proposed to me (" != " ) and the attested value, "IOS".
The advantage of using different (" != " ) rather than enumeration is twofold. First, it avoids a tedious enumeration of projects. Second, if you add new projects, their requests will appear without the need to maintain the filter.
Look for different types of applications in different projects
I'll take the example we had in the previous article(Why the basic Jira search is not enough). We wanted to retrieve all the requests of type "Story" from the Teams in space project and all the epics from the Web Browser App project. The difficulty we may have here is that "Story" and "Epic" are translated terms. But in the database, they are actually called "Story" and "Epic" (in English) because I installed my instance in English.
In the basic search, we use the terms in the translated language of the user. But in the advanced search, we use the field names as they are translated in the instance. So if you have trouble finding the right value, I advise you to use the basic search first and then translate your search in advanced mode. To perform my search, I will first proceed to the selection of the Teams in space project stories:
project = "Teams in space" and issuetype = Story
Then I will add the epics of the Web Browser App project. I will surround each criterion with brackets and associate them with the "Or" operator:
(Project = " Teams in Space " and issuetype = Story) OR (project = " Web Browser App " and issuetype = epic)
Once I have entered an advanced query that cannot be created in basic mode, the link that allows me to return to the latter mode appears in gray. In the examples we listed earlier, we saw that it was not possible to search numeric fields other than by entering the correct value.
Search for requests that contain a numeric field
In advanced mode, I can ask to retrieve all requests for the business value greater than 40 by writing the query :
"Business value > 40
I can also ask for all requests between 40 and 80 :
« Business value » > 40 AND « Business value » < = 80
So beware of one thing, if I search for requests with a business value greater than 40, I find 65.
"Business value > 40
If I search for business values greater than or equal to 40, I find 94.
« Business value » < = 40
If I look for all the applications, I find 741. When I might think there are 65 plus 94, so 159.
This is because searches only bring back requests for which the business value is worthwhile.
If I want all the requests that have a business value lower than 40 or that have no business value, I must enter "business value" lower than or equal to 40 OR business value is empty:
« Business value » < = 40 OR « Business value » is empty
And there, we see that it is indeed 676 requests. This also confirms that it is not possible to query Jira in basic mode to find empty values.
I want to emphasize that you are not taking any risks by testing different searches. Be interested in the advanced mode because after a while, the basic mode will not allow you to do more advanced searches. So now you know how to go beyond basic search and use Jira's advanced search. In the next article, we'll go further by exploring Jira's advanced search features.
