Search Jira with JQL:  Easier than it sounds

Jira Query Language

By: Rachel Wright, author of the Jira Strategy Admin Workbook

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.

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.

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 http://example-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 ANDstatus = 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 ORstatus = 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 PartExampleJQL

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 “order of 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.”

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!

 

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 the 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.

ExampleJQLUse
Assigned to meassignee = currentUser() AND status != Closed ORDER BY created ASCIssues assigned to me, that need action, ordered by oldest creation date (ascending order)
Created by mecreator = currentUser() ORDER BY created DESCIssues I created recently (descending order)
“Stale” issuesstatus != Closed AND updated <= -30d ORDER BY updated ASCIssues not closed that haven’t been updated this month
Not resolved by the due dateresolution is EMPTY AND due <= now()Unresolved issues with a “Due Date” in the past
Status changed this weekstatus CHANGED TO “To Do” AFTER startOfWeek() BEFORE endOfWeek()Issues currently in, or previously in a status, in the current week
Due this month or nextdue >= startOfMonth() AND due <= endOfMonth(1)Notice the “1” variable in the “endOfMonth(1)” function
Keyword or labeltext ~ reimbursement OR labels = reimbursementIssues with “reimbursement” in the “Summary” (title), “Description”, “Comment”, or “Labels” field
Without a labellabels != reimbursement OR labels is EMPTYIssues not labeled “reimbursement.”  Don’t forget to include the “OR labels is EMPTY” clause!
Linked issuesissue in linkedIssues(ISSUE-123)All issues linked to ISSUE-123
Linked issues by link typeissue in linkedIssues(ISSUE-123, “Relates To”)All issues related to ISSUE-123
Epic links“Epic Link” = ISSUE-456All issues associated with Epic ISSUE-456.  Enclose multiple works like “Epic Link”  in quotes.

Resources

Want to learn more?  Check out Rachel’s 30-minute online topic and skill-based training for Jira, Jira Service Desk, and Confluence at https://training.jirastrategy.com