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.

The Essential Spellbook to Unleash Advanced Jira Query Language (JQL) Magic

Download your free guide

Unlock the power of Jira Query Language.

Chapter 1 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.

Quick Search

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.

Jira Quick Search screenshot
Jira quick search bar gives you a list of possible results as you type.

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.

Basic Search

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.

Screenshot of Jira Basic Search bar
Jira basic search bar gives you a wide array of options and combinations to narrow down your search.

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.

Advanced Search

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.

Screenshot of Jira JQL search bar
The green check mark on the JQL search bar means that you’ve entered your query correctly.

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.

Chapter 2 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.

Fields

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.

Screenshot of search bar showing the assignee field in a Jira search
Start typing your field into the search bar and you’ll get auto-complete suggestions.

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

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.

Screenshot of dropdown operators menu in Jira JQL search
You’ll get a list of operators to choose from to refine your search.

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

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

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.

Screenshot of dropdown keyword menu in Jira Search
The dropdown menu suggests keywords to choose from.

Functions

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:

Example of Jira Query Language query including fields, operators, values, keywords and functions
Atlassian’s JQL Cheat Sheet (source).

These are the basics you need to know. Atlassian also covers the lengthy list of terms with slight differences depending on whether you’re using cloud or data center.

Chapter 3 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
Screenshot of extended search in Jira to find issues that haven’t been commented on
Easily find all issues that haven’t been commented on in the past few days to see if they need attention.

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.

Screenshot of “link” search in Jira advanced search
When you start typing in “linkedIssue,” you’ll have a choice of auto-complete options.

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:

linksIssueProject= JQL

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")

or

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:

remoteLinkApplicationName="Confluence"

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

Finding Outliers

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:

linksCount >=3

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:

subTasksCount >=4

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.

Screenshot of pie charts created by plugging a saved Jira query filter into a Jira dashboard
With filters, you can instantly generate 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):
    commentLastCreatedBy= filip
  • Find failing tests that are linked to your work:
    In the cloud version:
    issue in linkedIssuesOfQuery("assignee=currentUser()", "is tested by") and TestRunStatus=FAIL
    In 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)

Screenshot of Tempo app for Jira integration with JQL filters
Save your JQL query as a filter and plug it into Tempo to generate custom reports.

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
  • Text Search
    • ExactTextMatch
    • ExactTextMatchCaseInsensitive
    • WildcardMatch
    • Regex
  • Links- Relations and Dependencies
    • LinkedIssuesOfQuery
  • Epics Hierarchy
    • EpicsOfChildrenInQuery
    • ChildrenOfEpicsInQuery
  • Subtasks
    • ParentsOfSubtasksInQuery
    • SubtasksOfParentsInQuery
  • General Hierarchy
    • ParentsOfIssuesInQuery
    • ParentsOfIssuesInQueryRecursive
    • ChildrenOfIssuesInQuery
    • ChildrenOfIssuesInQueryRecursive
Additional Fields/Aliases in JQL Search Extensions
  • Searching for Attachments
    • AttachmentContent
    • AttachmentsCount
    • AttachedByUser
    • AttachedOnDate
    • AttachmentExtension
    • AttachmentName
  • Searching for Subtasks
    • ParentSummary
    • ParentPriority
    • ParentIssueType
    • ParentStatus
    • ParentStatusCategory
    • SubtasksCount
    • SubtaskSummary
    • SubtaskKey
    • SubtaskPriority
    • SubtaskIssueType
    • SubtaskStatus
    • SubtaskStatusCategory
    • SubtasksCount
    • SubtaskSummary
    • SubtaskKey
    • SubtaskPriority
    • SubtaskIssueType
    • SubtaskStatus
    • SubtaskStatusCategory
  • Searching for Versions
    • AffectedVersionsArchived
    • AffectedVersionsReleased
    • AffectedVersionsOpened
    • nsReleased
    • FixVersionsOpened
    • FixVersionsCount
    • FixVersionReleaseDate
  • Searching for Links
    • LinksCount
    • LinkedBy
    • LinksIssue
    • LinkType
    • LinkedIssueStatus
    • LinkedIssueStatusCategory
    • LinkedIssueType
    • LinkedIssuePriority
    • LinksIssuesCount
    • LinkedByIssuesCount
    • LinkedByIssueProject
    • LinksIssueProject
  • Searching for Remote Links
    • RemoteLinkUrl
    • RemoteLinkUrlPartialMatch
    • RemoteLinkApplicationName
    • RemoteLinkApplicationType
    • RemoteLinkHost
    • RemoteLinkQuery
    • RemoteLinkPath
  • Searching for Updates and Time Logs
    • UpdatedByUsersCount
    • UpdatedBy
    • TransitionedBy
    • LoggedTimeBy
    • UpdatedOnDates
    • LastUpdatedBy
    • MovedProjects
    • MovedProjectsCount
    • HasSameUpdatedAndCreatedDate
    • HasSameAssigneeAndReporter
    • HasSameVersions
  • Searching for Epics
    • issuesInEpicCount
    • bugsInEpicCount
    • storiesInEpicCount
    • issuesInEpicToDoCount
    • issuesInEpicInProgressCount
    • issuesInEpicDoneCount
    • bugsInEpicToDoCount
    • bugsInEpicInProgressCount
    • bugsInEpicDoneCount
    • storiesInEpicToDoCount
    • storiesInEpicInProgressCount
    • storiesInEpicDoneCount

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.

Ready to experience the power of JQL Search Extensions?

Try JQL Search Extensions for Jira for Free
The Essential Spellbook to Unleash Advanced Jira Query Language (JQL) Magic

Download your free guide

Unlock the power of Jira Query Language.

Appendix: Complete List of JQL Fields, Functions, Operators and Keywords

Fields
  • Affected version
  • Approvals
  • Assignee
  • Attachments
  • Category
  • Change gating type
  • Comment
  • Component
  • Created
  • Creator
  • Custom field
  • Customer Request Type
  • Description
  • Due
  • Environment
  • Epic link
  • Filter
  • Fix version
  • Issue key
  • Issue link
  • Issue link type
  • Labels
  • Last viewed
  • Level
  • Organization
  • Original estimate
  • Parent
  • Priority
  • Project
  • Project type
  • Remaining estimate
  • Reporter
  • Request channel type
  • Request last activity time
  • Resolution
  • Resolved
  • SLA
  • Sprint
  • Status
  • Summary
  • Text
  • Time spent
  • Type
  • Updated
  • Voter
  • Votes
  • Watcher
  • Watchers
  • Work ratio
Functions
  • approved()
  • approver()
  • breached()
  • cascadeOption()
  • closedSprints()
  • completed()
  • componentsLeadByUser()
  • currentLogin()
  • currentUser()
  • earliestUnreleasedVersion()
  • elapsed()
  • endOfDay()
  • endOfMonth()
  • endOfWeek()
  • endOfYear()
  • everbreached()
  • futureSprints()
  • issueHistory()
  • issuesWithRemoteLinksByGlobalId()
  • lastLogin()
  • latestReleasedVersion()
  • linkedissue()
  • linkedIssues()
  • membersOf()
  • myApproval()
  • myPending()
  • now()
  • openSprints()
  • organizationMembers()
  • parentEpic()
  • paused()
  • pending()
  • pendingBy()
  • projectsLeadByUser()
  • projectsWhereUserHasPermission()
  • projectsWhereUserHasRole()
  • releasedVersions()
  • remaining()
  • running()
Operator
Operator Example
= 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
Keywords
Keyword Example
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)

Contact Us

Address

International House,
64 Nile Street N1 7SR
London United Kingdom

Get In Touch