The Essential Spellbook
to Unleash Advanced
Jira Query Language (JQL) Magic
If your company has been using Jira for some time, there may be thousands of issues in your Jira instance. Jira makes it easy for project managers and team members to view and manage their tasks for a sprint, but sometimes things get lost in the backlog. When you’re looking for exact relationships between issues, past versions, and certain terms, Jira’s basic search engine falls short.
How can you get a quick look at what someone did on a previous version of a product months ago? What if you’re working on a tight deadline and need to quickly identify all blockers? Don’t you wish you could wave a magic wand and instantly find what you need? Jira Query Language (JQL) gives you an easy way to do this.
When you need to perform a very specific search, simple search options aren’t enough to narrow down the possibilities. That’s where JQL comes in. Once you learn the terms, you’ll have the magic words to instantly find nearly anything you’re looking for.
For software developers accustomed to programming language, the terms will probably be familiar. If you’re a project manager or administrator, they may look intimidating at first, but you’ll soon pick them up, and the auto-complete feature will help.
In this guide, we’ll walk you through how to use this powerful, flexible Jira feature so you can work more efficiently.
Download your free guide
Unlock the power of Jira Query Language.
Types of Searches
for Jira Wizards
First, let’s look at the types of searches you can do in Jira. For more general searches, you often won’t need to use JQL at all. The more specific your request, the more complex the query you need to enter gets.
A quick search is great for your most general queries. For this, you just enter a simple keyword to what you’re looking for in the main Jira search bar.
If you already know the issue key you want to find, just enter it, and you’ll go straight to the issue. If you don’t know, Jira enables Smart Querying and will show you a dropdown of what you may be looking for based on your keywords.
When you need to get a bit more specific, a basic search allows you to filter by project, type, assignee, status and more. You get to it by clicking in the small quick search bar and selecting “all issues” at the bottom of the list. Here’s what the basic search bar looks like.
Choosing the More option gives you over a hundred choices including FixVersion, Epic, Children of Epic, Completed Date. You can select as many as you like to narrow down what you want to find. With so many specific options available, why do you even need to use JQL? You’re still missing out on an array of time-saving uses without it.
For the most advanced searches, JQL allows you the greatest flexibility. You can use all the options you have in basic search and more. Plus, you can combine the options in more complex ways. A basic search looks for all the options together. With an advanced search, you can add other operators like NOT and OR.
JQL search gives you the freedom to enter all your parameters in the bar in order to pinpoint exactly what you need. It’s best to learn some terms and syntax before you begin using it.
The Structure of JQL:
Mixing the Magic Words
To get the full benefit of JQL, take a look at its basic structure. While a long string of JQL may look confusing at first glance, it makes logical sense when you break down its elements. It’s made up of five basic parts: Fields, Operators, Values, Keywords, and Functions.
A field is a piece of information related to an issue such as assignee, reporter, version, attachment, category, or user. This is the first thing you type into the search bar.
If you’re an administrator, you can also set up custom fields. Custom fields can help you further organize your issues so they’re more easily found and are specifically suited to your business.
Operators are symbols and words that show how terms are related. This gives you much greater flexibility than a basic search as it allows a more complex relationship between each of the search terms.
After you type in the field, various operators will be suggested.
For example, if the field is “assignee” and you choose = as the operator, that means you’re looking for all issues where the assignee includes whatever follows it.
assignee = Alice
shows you all issues assigned to Alice
assignee != Alice
shows you all issues EXCEPT those that are assigned to Alice
Values are the user-defined terms that point to the specific results you need. They’re usually the main object of your search. Examples of values would be a date, a person’s name, a project name, or even a word you want to find in the text of a summary, description, or comment. In the above example, “Alice” is the value.
Keywords work in a similar way to operators in that they show how search terms are related. After you enter the field, operator, and value, you can further narrow your search with a keyword like AND, OR, ORDER BY.
A function is a term that you can use to make calculations in your search that may vary in value, for example, status and dates. You add the variables inside the parentheses, for example, with membersOf(), you can put the name of a team or group in the parentheses, or with lastLogin(), you can fill in the login date.
Here’s an example of a JQL query that has all the five elements above:
Use Cases: Unleashing
Powerful JQL Magic
Now that you understand the basics of using JQL, let’s look at some practical ways to apply it. Whether you’re a Jira Service Management administrator, a project manager, or a team member, JQL can be used to stay organized and improve your productivity.
The JQL terms acceptable by Jira’s advanced search give you a wide range of search possibilities, but it still has limits. If these use cases aren’t enough to meet your needs, you might consider adding an additional app like JQL Search Extensions for Jira to take your searching to the next level. As with standard JQL, JQL Search Extensions also has a few differences between the cloud and server/data center syntax.
Here’s what you can do with advanced search and how you can take it farther if you choose an extended search app.
For Jira Service Management Administrators
Using JQL can help you provide better service to your customers as you can identify which issues require a customer response and which issues haven’t been resolved by a service agent.
For issues requiring a customer response, enter:
project="JQL Search Extensions" AND issuetype="Waiting for customer"
You can do this using advanced search. If this is something you check often, you can save it as a filter, so you don’t have to type it in every time.
But what if you want to know which issues were last commented on by the customer and haven’t been resolved yet? You don’t want to keep your customers waiting!
For that, you’ll need to use an app like JQL Search Extensions. With that, you can create the following query:
commentLastCreatedOnDate < -1d AND commentLastCreatedBy not in membersof(jsd-operators-staff) AND resolution is EMPTY
Using this, you can see that a staff member was not the last person to comment on it, so the customer must have been the last person to comment and is still waiting for a resolution.
Another useful query with JQL Search Extensions to find out which issues haven’t been commented on in the past few days, and thus, likely require a reminder to either the customer or the agent, is this one:
commentLastCreatedOnDate < -3d
For Jira Project Managers
If you’re a project manager, you can use JQL to find blockers and overlooked issues, as well as comparing versions and looking for unresolved bugs.
With advanced search, you can compare two versions of a software project using a query like this:
fixVersion in (v2.2, v2.4)
If you need to find out the status of issues that are linked, just start out by entering “linkedIssue” and complete your query.
Discovering How Issues Are Linked
Finding linked issues is helpful, but it’s not always enough. For example, you might need to know if some epics have accidentally been marked Done even though issues are still in progress. If you have JQL Search Extensions, you’ll have the additional function of ChildrenofEpicsinQuery. To use the function, you’ll first have to open the Extended Search bar. Here’s how you’d do that search:
issue in childrenOfEpicsInQuery("resolution is not empty") AND resolution is empty
You may also want to find external dependencies—issues from external projects that are linked to your team’s project. External dependencies can block or rely on issues in the current project. To find external issues, use this JQL Search Extensions app function:
If you need to find a high priority task that’s being blocked, here’s how you can do that with JQL Search Extensions:
In the server/data center version:
issue in linkedBy("is blocked by", "is related to", "project = JQL AND priority = Highest")
In cloud version, you can use these to get similar results:
issue in linkedIssuesOfQuery("project = JQL AND priority = Highest", "is blocked by")
issue in linkedIssuesOfQuery("project = JQL AND priority = Highest", "is related to")
Find Remote Links and Attachments
If issues include remote links in the description, finding these can be used for reference. You can find all issues with remote links to Confluence documentation with a query like this:
Perhaps you know an issue in a project has an important attachment, but you can’t remember what it’s called or which issue it’s attached to. You can find it with this:
attachmentsCount > 0
It’s also helpful to be able to search for outliers to identify problems you might not know you have. For example, an issue with many dependencies might mean that this issue is quite complex and needs to be resolved as soon as possible:
Another type of outlier that may be undesirable is an issue with many subtasks. With this query, you can identify issues that have more than a certain amount of subtasks and address them:
Regular Expression (Regex) Function
Another powerful use when you’re trying to compare certain points of different versions is the regex function. This gives you greater flexibility than a typical advanced search. Here’s what a regex search to only find issues that match versions between v.2.0 and v2.5 that are betas would look like:
issue in regex("fixVersion", "v2.[0-5]-beta")
Once you create a query like this, you can save it as a filter and plug it into a dashboard to generate charts and reports.
Report on Hierarchies in Scaled Agile Framework
When you’re working in a scaled agile framework (SAFe), different issue types will be assigned users depending on their level in the framework. In order to navigate the hierarchy and find all the issues under a key initiative, a useful JQL Search Extensions query is this one:
issue in childrenOfIssuesInQueryRecursive("project='ACME' and type=Initiative")
This finds all children of initiatives in the project as well as children of children and so on. You can use this query to generate a report to clearly demonstrate the relationship across the framework.
For Any Team Members Using Jira
JQL is not only useful for project managers and administrators; it’s also effective for any team member completing daily tasks.
For example, if you want to check your issues that still need to be approved by another user, you can use this:
approvals = approver(jsmith)
This can be done in advanced search, but if you have JQL Search Extensions, there’s a lot more you can do:
- Find issues assigned to you that were last commented on by other users (only available in the cloud version):
- Find failing tests that are linked to your work:
In the cloud version:
issue in linkedIssuesOfQuery("assignee=currentUser()", "is tested by") and TestRunStatus=FAILIn the server/data center version:
issue in linkedBy("is tested by", "assignee=currentUser()") and TestRunStatus=FAIL
- Find parents of your subtasks so you can understand how tasks are related:
issue in parentsOfSubtasksInQuery("assignee=currentUser()")
- Find unresolved subtasks of your stories:
issue in subtasksOfParentsInQuery("status='Done'") and status='To
Automation and Integrations
By saving a JQL query as a filter, you can apply it to dashboards and reports as we’ve already seen above. In addition to that, filters can be applied to automations. Here are just a few automation ideas:
- If you have Jira Service Management, automatically approve service requests which have 0 opened subtasks.
- When an issue is transitioned to “To Do” and the JQL Condition is issue in wildcardMatch(“component”, “ci*”)assign to a person working on a given component.
- Send weekly emails showing all blockers for a release:
issue in linkedIssuesOfQuery("issuetype = Story and version = 4.0")
A saved filter can also be integrated into apps like Tempo, for example to see how much time has been spent on a project: project = X and issue in linkedIssuesOfQuery(project = X)
Chapter 4 Conclusion
The Magic of JQL
Once you’ve mastered the use of JQL, you’ll greatly improve your efficiency using Jira. No matter how many issues there are in your instance, a quick query can get you exactly what you’re looking for. Saving queries as filters allows you to keep track of regularly occurring issues and to generate reports.
Understanding the basic structure of fields, functions, operators, keywords, and values gives you the building blocks you need. Over time, use of these will become automatic, but you can always refer to the list if necessary. Auto-complete also gives suggestions as for what logically might follow in your query.
Level Up with JQL Search Extensions for Jira
If you add JQL Search Extensions for Jira, the functions and fields work in the same way; there are just a lot more of them for greater flexibility.
Additional Functions in JQL Search Extensions
Links- Relations and Dependencies
Additional Fields/Aliases in JQL Search Extensions
Searching for Attachments
Searching for Subtasks
Searching for Versions
Searching for Links
Searching for Remote Links
Searching for Updates and Time Logs
Searching for Epics
With all these additional options available, it’s worth investing in an app to expand your search capabilities. As we’ve shown from the use cases, the extra search functions and fields have multiple applications to your daily work in Jira, whether you’re an administrator, a project manager, a developer or a team member.
Download your free guide
Unlock the power of Jira Query Language.
Appendix: Complete List of JQL Fields, Functions, Operators and Keywords
|= equals||assignee = Alice|
|!= not equal to||assignee != Alice|
|> greater than||priority > normal|
|< less than||duedate < 2021/05/20|
|>= greater than or equal to||priority >= normal|
|<= less than or equal to||updated <= “30d”(not updated in last 30 days)|
|IN same as using multiple =||assignee IN (Alice, John, Paul)|
|NOT IN same as using multiple !=||assignee NOT IN (Alice, John, Paul)|
|~ contains, only used with text fields||summary ~ win|
|!~ does not contain, only used with text fields||description !~ page|
|IS only used with EMPTY or NULL||assignee IS empty|
|IS NOT only used with EMPTY or NULL||description IS NOT empty|
|WAS used to find past info about issues, used with AFTER, BEFORE, DURING, ON)||assignee WAS Alice BEFORE 2021/05/20|
|CHANGED with AFTER, BEFORE, DURING, ON, BY, FROM, TO||CHANGED FROM “In Progress” TO “Done” ON 2021/06/04|
|AND||project = SOFT AND assignee = Alice (SOFT projects assigned to Alice)|
|OR||NOT reporter = Alice (issues reported by anyone except Alice)|
|NOT||project = SOFT AND assignee = Alice (SOFT projects assigned to Alice)|
|EMPTY||assignee is EMPTY (there’s no assignee)|
|NULL||dueDate is NULL (there’s no due date)|
|ORDER BY||dueDate = EMPTY ORDER BY created (sort all issues without a due date in the order in which they were created)|