Jira Query Language (JQL) truly extends the search capabilities in Jira – which we reviewed in our previous article Jira JQL functions: the ultimate guide. Yet there is a certain finesse to getting your queries just right in order to find the exact information you’re looking for, otherwise a small nuanced error can easily throw you off.
Are you ready to join the all-star Jira admins by stepping up your JQL skills? Here are five JQL hacks which ensures you’ll find what you need quickly and efficiently.
1. Find all issues after the creation of one specific issue
Sometimes it is useful to view all the issues which were created after a certain issue was made, rather than execute a query for after a specific date.
In Jira, issues are identified in a unique way from the key, composed of the project and a designated number (which is assigned incrementally for each issue created).
By using the keyword “Key”, you can compare the keys from the issues of a project to one defined issue. For example:
- Key >= VSD-30 will query all the issues created since VSD-30
Note: To find issues created or updated since a certain date or during a certain period, you can use the fields “Created” and “Updated.”
2. Find all queries which exclude a certain parameter
Executing queries that exclude a certain parameter can be tricky. Effectively, if you use the JQL function “not in” or “!=”, it allows you to find all the issues that exclude a parameter – yet the results will only be composed the issues which has a value in this field.
- duedate != 2017-3-10 – queries all the issues which the due date does not equal March 10th, 2017. Here, you will not get issues in your results in which the due date field is empty.
If the above is problematic, the solution is to add a clause to include the empty fields:
- duedate != 2017-3-10 or duedate is empty – this queries all issues which the due date does not equal March 10th, 2017, along with those issues which the due date field is empty.
3. Use an approximation search
In JQL, “~” is available for several fields to search a string of characters or a few characters; for example if the user is looking for a ticket concerning a VPN:
- description ~ vpn
Be aware that the search takes into account the spaces (obviously not between the quotation marks).
In Jira Software, it is not very practical that all your projects’ Sprints, which you have permissions, are all viewed at once. Therefore with the keyword “Sprint”, the “=” functions like a “~”. You can therefore search the sprint’s name by approximation.
- Sprint = “SP” – shows all the issues which the Sprint is, for example, “SP – Sprint 1” and “SP– Sprint 2”
4. Optimize your data by leveraging your projects’ categories
Tired of selecting the same 10 projects? Using categories in your JQL query is helpful, as it can aggregate a set of projects together.
For example, instead of:
- Project in (“Mobile Apps”, “DS Agile”, “Market”, “Marketplace”, “Configuration”, “GlobalSupport”, “GeneralFinance”, “Support “, “HumanRessources”, “Databases”)
You can use:
- Category = “Internal department”
Note: If you can’t find the category that you are looking for, ask your Jira administrator if categories are used for your projects.
5. Queries on multiple fields
When a JQL query is executed on multiple fields, it is necessary to consider the “=” like the verb “contains”.
For example, issues may contain zero, one or multiple components. For this use case, let’s say we have five components: Jira, Crowd, Hipchat, FeCru and Bamboo. As such:
- component = Jira or component is empty – will query all issues that contain Jira as a component, alongside all issues in which the component is undefined. Likewise, if an issue has multiple components – let’s say both Jira and Crowd – this query will in fact include this issue with multiple fields.
Now let’s go back to the exclusions queries detailed in hack #2 and try this with multiple components:
- component ! = Jira or component is empty – in a nutshell this query is the exact opposite of the above example. It will search for all issues that do not include Jira as a component – even if the issue has multiple components – and will also exclude issues where the components field is undefined.
Using the same example, if I want to list all the queries concerning the components Crowd, Hipchat, FeCru and Bamboo, thus excluding all the queries with Jira as a component. If I try to execute the query through exclusion, I will miss out on a query related to both Jira and another component – for example Jira and Crowd. It will then be necessary that I carry out the following query to be sure to get all the components which I want to search for:
- component in (Crowd, HipChat, FeCru, Bamboo)
JQL search in Jira is full of subtleties. If you’d like to get a better handle over your JQL, our Atlassian Certification Preparation – Jira Administration course covers all the advanced aspects of JQL and prepares you for ACP-JA certification exam.