Video Tutorial: JQL Advanced – Enhanced Search Queries in Jira
JQL (Jira Query Language) is a powerful tool which allows you to base your search on criteria that you can’t specify in the basic search. The basic search provides the possibility to do this search with the dropdown menu but sometimes it reaches its limit. In this case, you can switch to advanced search which offers you tons of possibilities for special search functions. This article is intended to be a practical guide for anyone working with Jira, regardless of experience. If you’d like to see the operations described below in action, go ahead and check out our video tutorial.
Jira uses JQL filters in various places. Immediately after logging in, you can see a dashboard with different gadgets, most of which can be configured with the help of JQL filters. JQL filters are also used to determine which issues will be displayed on Kanban and Scrum boards.
First of all, let’s have a look at the standard search. Use the blue bar at the top and click Issues→ Search for issues to get to the issue navigator.
Sometimes you need to set a field value in many issues. To do this, use the bulk change. With a JQL query, you can filter out the required issues and carry out the bulk change using Tools in the top right-hand corner.
In the standard search, you can select values for different dropdown fields. It is possible to add system and custom fields via More. For example, if you check the Resolution field, it will also appear as a dropdown menu and you will be able to select the values you need, such as ”Unresolved” and “Done”.
We recommend switching to List View for a better overview. You can also add the Resolution field as a column. Thus, if you select “Scrum Project” for the Project field, “Current user” for Assignee, and “Done” and “Unresolved” for the Resolution field, you will see all the issues from Project SP that are assigned to you and whose resolution is “Done” or “Unresolved”.
Now, let’s switch to advanced search. Click on Advanced and you will see a bar with a JQL query. In our example, it will be:
project = SP AND resolution in (Unresolved, Done) AND assignee in (currentUser0)
From this point of view, it is a translation of the search query that you created earlier in the standard search mode.
A JQL query consists of five components. The smallest unit of a search query consists of a field which is compared by an operator with a field value or a function. We have three such units in the current search query. The project field is compared with the SP field value using the “Is equal to” operator. The next unit compares the resolution field with the values ”Done” and “Unresolved”. The third unit checks the field assignee with the current user. The current user is represented by a function. Functions can be recognized by a word and parentheses. Several of these units are linked with keywords. In our example, the units are linked logically with the AND keyword.
Let’s take a closer look at individual components. Fields refer to Jira system fields or custom fields. If you double-click on “project”, you will see a drop-down menu where you can choose one of the possible fields.
The next component is the operator. In our example, the first unit uses the EQUALS sign (“=”). It allows us to search for a specific value for the field. The operator in the other two units is IN, which also means “equals”, however, it is followed not by a specific value, but by a list of possible values (In our example you can see it for the resolution field where the values “Unresolved” and “Done” are chosen).
project != SP
By placing an exclamation mark or a NOT, you will receive the issues that are excluded from the project (in our example) or the issues that are not assigned to you.
created < 2020/03/16
If you are looking for number or date fields, you can also use “greater than” and “less than” characters. For example, if the creation date should be earlier than March 16, 2020, we say created less than 2020/03/16.
created > “-10d”
It is also possible to display all the issues that have been created in the last 10 days, for example. In this case, you write not a specific date, but a period of time. The “-” or “+” sign determines whether the current value lies in the past or in the future.
summary ~ “board”
You can also search text fields for words using the contains operator. For example, if we search for issues that have the word “board” in the summary, our JQL looks like this: summary ~ “board”.
If you are not sure of the word spelling or don’t want to type the whole word, you can go for a wildcard search, using a question mark for one of the characters or an asterisk for several characters.
assignee IS EMPTY
There is also an operator IS, which is used together with the keyword EMPTY (→ is or is not ). This checks whether a field is empty, i.e. without a value, or not. In other words, it allows you to search for issues that are not assigned to anyone.
We have already used the first keyword here: EMPTY. Now, let’s focus on other keywords. The main keywords are AND and OR:
- AND link means that all the linked conditions must be met.
- With an OR link, either one or the other condition must be fulfilled for an issue to appear in the list of filter results.
You can use AND and OR together for mixed results. For example, project = SP AND resolution in (Unresolved, Dome) OR assignee in (currentUser0) means “all tasks from Project SP that have the resolution “Unresolved” or “Done” and all tasks that are assigned to me, including those from other projects”.
You can make your JQL query even more complex by using parentheses. They always take precedence over keywords. Thus, project = SP AND (resolution in (Unresolved, Done) OR assignee in (currentUser0)) means that you are searching for issues that relate only to Project SP and either have the resolution values ”Done” / “Unresolved” or are assigned to you.
The last keyword we are going to address is ORDER BY, which allows you to sort the issues according to the desired field. For example, here: project = SP AND (resolution in (Unresolved, Dome) OR assignee in (currentUser0)) ORDER BY updated DESC, the results are ordered by update date. Note that DESC stands for descending and ASC for ascending.
The final part of a JQL query is a function. There are a lot of different functions, but we are going to focus on the frequently used ones:
- startOfWeek(), for example, created > startOfWeek(-2) shows the tasks that were created on Monday two weeks ago.
- endOfWeek, for example, duedate < endOfWeek(2) shows the tasks that have to be completed by Sunday in two weeks. When you double-click on endOfWeek, a drop-down appears that shows the possible functions for time information.
In addition to temporal functions, there are also functions for people. We have already come across the currentUser, which stands for the user who is currently logged in.
If you are looking for assignees who belong to a certain group, you can use membersOf() and specify the group name in brackets.
Other options are the functions for versions. Thus, you can filter for issues that are contained in the published version of a project. The project key is written in brackets, for example: fixVersion in releasedVersions(“SP”)
updatedBy() is a function which allows you to find all the issues that have been updated by a certain user (whose name is specified in brackets).
Once you have put together your JQL query, you can save it and edit the permissions. Under Details, you can find out and adjust who can see this filter and who can edit it. You can also subscribe to filters and determine when the results list is sent to you or the members of a group.
We have covered some of the commands in the Advanced JQL search mode. For more information, check out the official Atlassian documentation.
Efficient as it may be, Jira has its limitations when it comes to some search queries. If you want to use the wildcard search not only for text fields but also for dropdown fields or label fields, then our PowerJQL plug-in is just for you. It allows you to search for different document types in the attachment of an issue, and more. Start your free trial now and enjoy all the benefits of extended search functionalities.
Check out our other articles:
- GitHub Enterprise integration with Jira: a step-by-step tutorial
- CCPA vs. GDPR: data privacy laws in Europe and the USA
- 5 tips to make work from home work for you
- Video Tutorial: Portfolio for Jira
- Safe Password Management For Users And Administrators