What are JQL Queries?
According to techterms.com, a query is another word for a question. Each time you use a search engine, you’re querying the internet for web pages. When you search in Jira, you’re querying the database for issues that match your specifications. In Jira, JQL means “Jira Query Language” and it’s the way to search through thousands of issues to find the few you’re looking for. It’s also the way to control which issues appear on your board and what data is displayed in your dashboards and reports. JQL is for everyone, not just technical users. If you’ve ever used SQL, you’ll find JQL is similar. If you haven’t, it’s OK, we’ll show you how to use it.
You may already know that you can enter a specific phrase or issue ID in Jira’s search bar. But what if you don’t know the issue ID or need to find multiple issues? What if you only know the issue was created yesterday? Or that the issue was created by your team member? Or that the issue was placed in the backlog last November? Use a JQL query to easily filter your long issue list.
Basic Search
In addition to the search bar, there are two additional Jira search methods. In Jira Server, click the “Issues” link in the top main navigation and select “Search for issues”. In Jira Cloud, click the magnifying glass in the left side navigation and click “View all issues”. The first time you visit the search page, you’ll see the “Basic” search mode.
Image: Basic Search Mode
Basic search has a series of drop down boxes labeled “Project”, “Type”, “Status”, etc. Below the drop down boxes are all the issues in the Jira application. Use the search boxes to refine that long list until you’ve found the issues you’re looking for. First, if you know which project the issue is in, filter the issue list with that information. In the “Project” drop down type the project’s name, key, or scroll through the selections. Click the corresponding checkbox when you find it. If you don’t know the name of the project, just move to the next drop down. Use the menu to select as much information as you can. Each time you add a selection, the issue count decreases and the results list is more manageable. Use the “Contains text” box to find an issue with a word or phrase in its “Summary” (title), “Description”, or “Comments” field. Finally, select additional fields and criteria by clicking the “More” button.
TRY IT
Let’s search for bugs fixed within the last month. We know three pieces of information: (1) The type of issue we’re looking for is “Bug”, (2) the status is “Closed”, and (3) the creation date is fairly recent.
- Visit the search page at: your-jira-url.com/issues/
- In the “Type” field, select “Bug”
- In the “Status” field, select “Closed”
- In the “More” field, check the box next to the “Created” field
- In the “Created” field, limit the search to issues created “within the last 4 weeks”
- Finally, click the “Update” button
Great! You just narrowed the issue list down considerably. If you received the message “No issues were found to match your search” that’s OK. It just means there are no issues that match the sample criteria. Maybe you have an awesome development team that never has any bugs! If so, change the search criteria until you get a list of results. Try the “Task” type, instead of “Bug”, or the “Open” or “New” status, instead of “Closed.”
Advanced Search
Before clearing the results of the “Basic” search, click the “Advanced” link to the right of the magnifying glass. Instead of the drop down boxes, there’s a long text field. In the field is the search you just did, but in JQL format. It might look like this:
issuetype = Bug AND status = Closed AND created >= -4w
There’s the same three pieces of known information again: the type, the status, and the created date of less than 4 weeks. You can switch between “Basic” and “Advanced” search mode at any time. Jira will remember which you used last. The “Advanced” mode is much more powerful than “Basic”, so I encourage you to try it. Once you understand the query format, it’s easy to find what you’re looking for.
Query Format
All queries start with a field, followed by an operator (like the “=” sign), followed by one or more values. For example:
issuetype = Bug
In the example, “issuetype” is the field, “=” is the operator, and “Bug” is the value.
Image: Search clause
You can combine multiple search clauses by adding “AND” or “OR” between them. For example:
issuetype = Bug AND status = Closed
In the example, there are two clauses. You can add as many as you need to get to the data you’re after. Next, try changing “AND” to “OR” to see how that affects your results.
TRY IT
Change “AND” to “OR” and compare the results
- Visit the search page at: your-jira-url.com/issues/
- If not in “Advanced” search mode, click the “Advanced” link to the right of the magnifying glass
- In the text box, enter: issuetype = Bug AND status = Closed
- Hit the “Enter” key or click the magnifying glass to execute the search and review the results
- In the text box, enter: issuetype = Bug OR status = Closed
- Hit the “Enter” key or click the magnifying glass to execute the search and compare the results
Additional issues were likely returned by your second query. Changing “AND” to “OR” changed the scope of the search. The results of the second query contain all bugs (regardless of their status) and any other issue types in the “Closed” status.
Query Format Examples
Here are some examples of common fields, operators, values, and conditions.
| Clause Part | Example | JQL |
|---|---|---|
| Field There are likely hundreds of fields you can use in queries. Here are some common examples.1 Note: You must enclose multiple words in quotes. | project | project = “Legal Team” project = “LEGAL” |
| issuetype | issuetype = Bug issuetype = Task issuetype = Sub-task | |
| assignee | assignee = username assignee = currentUser() | |
| reporter | reporter = username reporter = currentUser() | |
| status | status = Closed status = “In Progress”1 | |
| Operator The operator to use depends on the field. Jira will suggest operators as you type. | Equals (“=”) Not Equal (“!=”) Greater/Less Than (“>” and “<“) Greater/Less Than or Equal To (“>=” and “<=”) Is (“is”) Is Not (“is not”) In (“in”) Not In (“not in”) Like (~) etc. | status != Closed created >= 2018-11-01 text ~ “system error” project in (“LEGAL”, “HR”) |
| Values There are thousands of possibilities like: a username, a number, a phrase, a selection value, etc. There are also special built in date related functions. | john.doe 2019-01-01 High Functions: startOfDay() startOfWeek() startOfMonth() endOfYear() now() Etc. | reporter = john.doe due = 2019-01-01 priority = High created > startOfMonth() |
| Conditions Use conditions to string clauses together.2 Note: Just like the “orderof operations” concept from math class, use parenthesis to group related conditions. | AND OR Etc. | status = Open OR status = New (status = Open OR status = New) AND created > startOfWeek()2 |
Auto Suggestion
As you type, Jira will help you correctly format your query. It will suggest fields, operators, and values. When expected information is missing, you’ll see a red “x” or “!”. When all is well. you’ll see a green “check mark.”
Image: Auto suggestion and green check mark
In addition to format errors, there are also data errors. When you receive an error, read the message for clues to the problem. In the example, the green check mark shows the query format is correct, but the error message says there’s no Issue Type called “Reimbursement.” Maybe your Finance team should start accepting reimbursement requests in Jira!
Image: Correct format but data problem
Narrow the Results
Finding a particular issue, within a gigantic list, can be quite easy. Think about what information you already know that could limit the results. Do you know what project the issue is in? Do you know who created the issue? Do you know when the issue was created? Do you know a word or phrase in the “Summary” (title) or “Description”? Use or combine this information to quickly find what you’re looking for. Keep adding clauses until all the info you have is exhausted, you’ve found the issue, or you’ve limited the results to a reasonably-sized list.
TRY IT
You want to find an issue you created for the Finance team this year. You remember that they addressed your request and it was related to a travel reimbursement. This is a lot of good info!
Use the following clauses to limit the results:
- Issue is in the Finance project
- Issue was created by you
- Issue was created this year
- Issue is closed
- Summary contains the word “reimbursement”
What does your query look like? Here’s mine: project = Finance AND reporter = currentUser() AND created >= startOfYear() AND status = Closed AND text ~ “reimbursement”
The currentUser() variable is helpful for sharing queries with multiple users. If this query is only for you, you can substitute your username as the reporter value.
The “~” sign represents a “like” search. If finds issues containing a single word or phrase. You can also exclude words or perform wildcard searches.
Query Tips
- The easiest way to diagnose query problems is to break up long queries into individual clauses. For example start with “issuetype = Bug” and execute the search. If the search returns result successfully, then add a second
clause and execute the search again. This will help isolate the source of the issue. - Always sanity check the results of your search. Even an incorrect query can return results! A quick way to catch issues is to make sure all fields you are searching are shown as columns on your search results page. Click the “Columns” menu on the right to add any that are missing.
- The quickest way to limit the results list is often by specifying a Jira project. Begin your query by excluding all the unneeded projects with: project = “project name”.
Example Queries
Use these sample JQL queries as a starting point and further customize them to meet your needs. There are lots of neat things you can do with JQL! Try changing the values and combining different clauses.
| Example | JQL | Use |
|---|---|---|
| Assigned to me | assignee = currentUser() AND status != Closed ORDER BY created ASC | Issues assigned to me, that need action, ordered by oldest creation date (ascending order) |
| Created by me | creator = currentUser() ORDER BY created DESC | Issues I created recently (descending order) |
| “Stale” issues | status != Closed AND updated <= -30d ORDER BY updated ASC | Issues not closed that haven’t been updated this month |
| Not resolved by the due date | resolution is EMPTY AND due <= now() | Unresolved issues with a “Due Date” in the past |
| Status changed this week | status CHANGED TO “To Do” AFTER startOfWeek() BEFORE endOfWeek() | Issues currently in, or previously in a status, in the current week |
| Due this month or next | due >= startOfMonth() AND due <= endOfMonth(1) | Notice the “1” variable in the “endOfMonth(1)” function |
| Keyword or label | text ~ reimbursement OR labels = reimbursement | Issues with “reimbursement” in the “Summary” (title), “Description”, “Comment”, or “Labels” field |
| Without a label | labels != reimbursement OR labels is EMPTY | Issues not labeled “reimbursement.” Don’t forget to include the “OR labels is EMPTY” clause! |
| Linked issues | issue in linkedIssues(ISSUE-123) | All issues linked to ISSUE-123 |
| Linked issues by link type | issue in linkedIssues(ISSUE-123, “Relates To”) | All issues related to ISSUE-123 |
| Epic links | “Epic Link” = ISSUE-456 | All issues associated with Epic ISSUE-456. Enclose multiple works like “Epic Link” in quotes. |
What is your most useful query? Share it in the comments section.
Resources
- Basic searching: https://confluence.atlassian.com/jiracoreserver/basic-searching-939937684.html
- Advanced searching: https://confluence.atlassian.com/jiracoreserver/advanced-searching-939937709.html
- Search syntax for text fields: https://confluence.atlassian.com/jiracoreserver/search-syntax-for-text-fields-939937723.html
Want to learn more? Check out Rachel’s 30 minute online topic and skill based training for Jira, Jira Service Desk, and Confluence at: training.jirastrategy.com