Power up your search with JQL

Finding what you're looking for can be quite cumbersome in Jira, especially across multiple projects and issues. The standard search functionality offers you a text field where you can type what you're looking for. This is sufficient for most cases where you know the title or the key of an issue. But what if you need more fine-grained control over the searches you make? How do you search for issues created this month? Or something even more specific like limiting to only the bugs created this month?

Jira's Query Language, JQL, to the rescue!

JQL allows you to efficiently search throughout Jira and you don't have to be technical to be able to use it. Just like advanced searching in Google, you just have to know a few techniques to boost your efficiency when searching.

In order to use JQL, you need to engage advanced search in Jira. When you can use filters to search for issues, you almost always find an "Advanced" link that will take you to the advanced search. To open the advanced search:

  • Navigate to the issue search in Jira
  • Find the filters at the top of the page
  • On the right-hand side of the filters, click on the "Advanced" link

The filters will be replaced by an input field. This is the advanced search field in which you can use JQL.

Let's try search for all issues created this month with JQL. This would translate to the following JQL:

createdDate >= startOfMonth()

You can save this search with a title such as "Issues for current month" and the next time you're on the advanced search page you can easily search for the same by clicking on the saved search without typing the JQL again.

Let's explore the JQL

JQL is built on four simple building blocks: Fields, Operators, Functions and Keywords.

Fields

Fields are words that represent a Jira field, such as 'createdDate'. We used the 'createdDate' field to specify we wanted to target the date that issues were created.

Operators are one or more symbols or words used to compare a field on the left side with one or more values or functions on the right, such as '>='. The '>=' operator is used to specify that you want to filter issues based on the field on the left side which are greater than or equal to the value or function on the right.

Functions are words followed by parentheses, such as 'startOfMonth()'. Functions calculate dynamic data and return a result which is indicated by the name of the function. So knowing this the JQL can be read as: Find all issues which are created at or later than the beginning of the current month.

Before we dive into keywords, let's play around with the JQL a bit to see the power of fields, operators and functions. There are a few things to note when writing JQL in the search field:

  • Notice that as you are typing JQL you will see an icon indicating if your JQL is valid. Use this to your advantage to spot if the JQL you wrote is valid. Otherwise your search will not work.
  • Another nice thing when exploring JQL is to write a few characters and autocomplete based on JIRA's suggestions.

How would we change the previous JQL to only show issues which are updated this month? The previous JQL used the 'createdDate' field. We can simply change our JQL to

JQL autocompletion and syntax validation

How would we change the previous JQL to only show issues which are updated this month? The previous JQL used the 'createdDate' field. We can simply change our JQL to

updatedDate >= startOfMonth()

How about finding all issues which haven't been updated since the beginning of this month? Or this year? We could achieve that by change our JQL respectively to

updatedDate < startOfMonth()` AND `updatedDate < startOfYear()

I hope you see how easy it is to quickly create powerful JQL expressions.

Combining JQL

The JQL until this point focused on a single statement, but what if we wanted to combine multiple statements? How can we search for issues which are created this month AND filter on bugs? That's where the 4th building block 'Keywords' come in. Keywords are words or a phrase that can join or alter the logic of multiple statements together. With this knowledge we can expand our JQL with the following:

createdDate >= startOfMonth() AND issuetype = Bug

Here the `AND` keyword binds two statements together. You can combine `AND` keywords with statements for as long as you want. Besides the `AND` keyword the `OR` keyword is really common. We can get all issues which are created this month and are either a bug OR a feature with the following JQL:

createdDate >= startOfMonth() AND (issuetype = Bug OR issuetype = Feature)

The parentheses make sure that the right hand side of the `AND` is evaluated together. Read more about operator precedence. The JQL without parentheses:

createdDate >= startOfMonth() AND issuetype = Bug OR issuetype = Feature

This would translate to: Find all issues which are created at or later than the beginning of the month AND have their issue type set to "bug" or get all the issues with a type set to "feature". While the JQL with the parentheses translates to: Find all issues which are created at or later than the beginning of the month AND have their issue type set to either "bug" OR "feature". You can also express the same with a slightly different JQL:

createdDate >= startOfMonth() AND issuetype in (Bug, Feature)

Here we make use of the 'IN' keyword which checks if the field on the left hand side is also in the list (denoted by parentheses) on the right hand side of the operator.

JQL with Atlas CRM

Customer Relationship Management add-on Atlas CRM also makes use of JQL. Atlas CRM keeps all your customer information and documentation organized and easily accessible in JIRA and Confluence so you can focus on making your customers happy.

Because of Atlas CRM's integration with JIRA you can make use of JQL to search for Atlas CRM companies and contacts. The great thing about this integration is that JQL autocompletion for your companies and contacts also works! For example, let's search for all issues linked with Atlas CRM to my colleague Kristina with JQL. We can use the simple JQL:

contact = "Kristina Meelind"

This search will retrieve all the issues linked to Kristina. A really nice example of the power of Atlas CRM with JQL is to search for all issues created by a company on your Service Desk. We could accomplish this with the following JQL

company = "Avisi B.V." AND project = "Atlas CRM Service Desk"

You can expand upon this JQL and save it for later use!

Reference

In order to further explore these concepts I would suggest looking at the available Fields, Operators, Functions and Keywords. A list of all the Fields, Operators, Functions and Keywords to use are listed below with a reference to the documentation for further exploration.

Fields

- Affected Version

- Assignee

- Attachments

- Category

- Comment

- Component

- Created (same as createdDate)

- Creator

- Custom Field

- Description

- Due

- Environment

- Epic Link

- Filter

- Fix Version

- Issue Key

- Labels

- LastViewed

- Level

- Original Estimate

- Parent

- Priority

- Project

- Remaining Estimate

- Reporter

- Resolution

- Resolved

- Sprint

- Status

- Summary

- Text

- Type

- Time Spent

- Updated (same as updatedDate)

- Voter

- Votes

- Watcher

- Watchers

- Work Ratio

More searchable fields

Operators

EQUALS: =

NOT EQUALS: !=

">GREATER THAN: >

=">GREATER THAN EQUALS: >=

LESS THAN: <

LESS THAN EQUALS: <=

IN

NOT IN

CONTAINS: ~

DOES NOT CONTAIN: !~

IS

IS NOT

WAS

WAS IN

WAS NOT IN

WAS NOT

CHANGED

More operators

Keywords

- AND

- OR

- NOT

- EMPTY

- NULL

- ORDER BY

Note: You can alter the order of execution with parentheses.

More keywords

Functions

- cascadeOption()

- closedSprints()

- componentsLeadByUser()

- currentLogin()

- currentUser()

- earliestUnreleasedVersion()

- endOfDay()

- endOfMonth()

- endOfWeek()

- endOfYear()

- issueHistory()

- issuesWithRemoteLinksByGlobalId()

- lastLogin()

- latestReleasedVersion()

- linkedIssues()

- membersOf()

- now()

- openSprints()

- projectsLeadByUser()

- projectsWhereUserHasPermission()

- projectsWhereUserHasRole()

- releasedVersions()

- standardIssueTypes()

- startOfDay()

- startOfMonth()

- startOfWeek()

- startOfYear()

- subtaskIssueTypes()

- unreleasedVersions()

- votedIssues()

- watchedIssues()

Find out more on functions

Happy searching!

Written by