Jira JQL functions: the ultimate guide


Posted by
Balkis KHOUNI

September 14, 2017

If you are a Jira user or administrator, you probably already tried using the search feature, either to look for a specific issue or a list of issues. It can range from being as simple as selecting an option from a drop-down list to a feat that is very complex and confusing and requires knowledge of JQL.

This article will tell you about tricks you can use to create advanced search queries using JQL – Jira Query Language. The interesting thing about a JQL search is that you can save it as a Filter for several purposes: Subscriptions, Dashboards, Agile Boards, Project Portfolio plans along with other app contexts! In some cases, you will have to elaborate a JQL query to create a filter that groups the exact issues you want to show on your dashboard or board.

Before we dive into JQL tricks, I would like to list the different ways you can perform an issue search in Jira.

Jira search features

Quick search

This is the most obvious search feature as it can be accessed from the application header. The quick search is used to look for issues. There are three possible ways of using this search box:

1 – Free-text searching

If you type any word (that is not a keyword for Jira), Jira will search in the Summary, Description and Comments fields of the issue. You will then be redirected to the Issue Navigator with the list of the issues which match your search.

2 – Jumping to an issue

If you type the issue key (e.g. TP-146), you will be redirected straight to that issue. Obviously, the issue must already exist and you need to have permission to view it.

3 – Smart JQL querying

With this type of search, you can look for really specific issues by typing very short queries. There are a lot of keywords and tricks you can learn here to be able to optimise your time while looking for issues.

Here is an example of a smart query:

FL Open Story

If you have a project with the key FL and a status called Open and an issue type called Story, the previous query will be translated by Jira as the following JQL query

project = FL AND issuetype = Story AND status = Open

Basic JQL search

Basic JQL search is recommended if you have never user Jira Query Language and want a simple way to filter issues. It can be performed from the Issue Navigator.

To access it: From the application header, choose Issues > Search for Issues.

The Basic Search bar looks like this:

In case you don’t see it, it is probably because you are in the Advanced mode. To switch to the Basic mode, use the Basic link that is on right of the search bar.

By selecting options in the drop-down menus, you will be able to narrow your search. You can even add more fields to search by using the More menu.

Advanced JQL search

The advanced search, on the other hand, is a field with autocomplete feature that gives you the flexibility to elaborate a query using JQL.

Any basic search can be translated to a JQL query by switching to the Advanced mode from the Basic mode. There are several reasons you would want to switch to the advanced search mode:

  1. If you want to use the OR logical operator.
  2. When a certain field is not supported by the Basic search mode. An example of an unsupported field is the Time to First Response SLA field. By trying to add the field to the basic search, you will be asked to switch to the advanced mode.
  3.  If you want to use JQL functions – such as the ones introduced below.

JQL examples: commonly requested filters

Linked issues

All linked issues

If you want to get the list of issues linked to a specific issue, use the built in JQL function linkedIssues(issueKey).

issue in linkedIssues("TP-345")

Linked issues by link type

If you want to get the list of issues linked to a specific issue with a specific link type, use the built in JQL function linkedIssues(issueKey, linkType).

issue in linkedIssues("TP-345", blocks)

To get the link type, you will need to be a global administrator. Go to:

  1. Administration () > Issues
  2. Under Issue Features section, choose Issue Linking
  3. Use the Name column to get the Link Type

You will find the list of all link types available in your Jira instance there.

The functions above can be quite useful if you want to list the issues that are linked to a specific issue. This means you will need to have the key of the issue.

All issues by link type

For more advanced queries, you will need to install the ScriptRunner app.

If you want to list all issues that have linked issues with a specific link type, use hasLinkType(linkType) function.

issueFunction in hasLinkType(Blocks)

When it comes to ScriptRunner JQL functions, we do not use issue object, we use issueFunction, which explains the query above. The following query is wrong:

issue in hasLinkType(Blocks)

Issues linked to filtered issues

If you want to get the list of issues linked to a specific set of issues (filter of issues), use linkedIssuesOf(Subquery) function.

issueFunction in linkedIssuesOf("project=IDP")

You can add a specific link type if you want to get the list of issues linked to a specific set of issues with a specific link type. To do that, use the function linkedIssuesOf(Subquery, linkType) .

issueFunction in linkedIssuesOf("project=IDP", Blocks)

If you want to include epic links and subtasks, use linkedIssuesOfAll(Subquery) function.

issueFunction in linkedIssuesOfAll("project=IDP")

When you specify the link type, the following function will return the same result:

  • linkedIssuesOfAll(Subquery, linkType)
  • linkedIssuesOf(Subquery, linkType)

Issue hierarchy queries

Nested queries are one of the most requested features in Jira. Although it is not available out of the box, you can still get it in an app like ScriptRunner.

There are multiple contexts where you get to elaborate nested queries, but the most common use has to be epic hierarchy.

Let’s start from the bottom and move up the hierarchy.

Subtask queries

The out of the box way to get the list of subtasks in Jira is by using the object Parent. However, you will need to specify the parent issue key(s).

parent = "IDP-4689"

OR

parent in ("IDP-4689","IDP-4687")

If you want to get the list of subtasks for a subset of issues (using a subquery), you will need an add-on that implements this feature to your Jira. Using ScriptRunner, we can achieve this by using issueFunction in subtasksOf(Subquery) function.

issueFunction in subtasksOf("project = IDP AND issuetype=Incident")

Parent queries

There is no out of the box JQL function that allows you to get parent issues of a subset of subtasks. Below, we share an alternative option using the ScriptRunner app.

If you want to get the parent issues of a subquery, you can use parentsOf() function.

issueFunction in parentsOf("project=IDP and issuetype=Incident")

Epic/Story queries

Stories in epics

The out of the box way to get stories under epics is by using the custom field Epic Link.

"Epic Link" in (SCUR-24,SCUR-25)
The limitation here is that you have to specify the Epics issue keys. We will see further down how you can have a more generic query.

In case you are looking for a more generic query where you define epics using a subquery, you can use the ScriptRunner app. The function to use is linkedIssuesOf(Subquery).

issueFunction in linkedIssuesOf("issuetype = Epic", "is epic of")

Epics for stories

There is no out of the box feature if you need to get epics for a subset of issues (stories). Yet using ScriptRunner, you will be able to achieve this using epicsOf(Subquery) function.

issueFunction in epicsOf("project = IDP AND issuetype=Incident")

Epics and stories

The built-in way of getting epics as well as stories of those epics is by using Epic Link custom field along with logical operators.

"Epic Link" in (SCUR-24, SCUR-25) OR issuekey in (SCUR-24, SCUR-24)
The limitation here is that you have to specify the Epics issue keys. We will see further down how you can have a more generic query.

If you are using ScriptRunner, you will be able to elaborate a more generic JQL query using linkedIssuesOf(Subquery) function.

(project = IDP AND issuetype = Epic) OR issueFunction in linkedIssuesOf("project = IDP AND issuetype = Epic", "is epic of")

The following bit filters epic issues:

(project = IDP AND issuetype = Epic)

The following bit filters stories in the epics above:

issueFunction in linkedIssuesOf("project = IDP AND issuetype = Epic", "is epic of")

Epics, stories and subtasks

There is no built-in way to get the three levels of hierarchy using a JQL query. The challenge here is obviously to get the subtasks of the stories returned by the first bit of the query.

However, this can be done using ScriptRunner. The query can be pretty heavy though.

(project = IDP AND issuetype = Epic) OR issueFunction in linkedIssuesOf("project = IDP AND issuetype = Epic", "is epic of") OR issueFunction in subtasksOf("issueFunction in linkedIssuesOf('project = IDP AND issuetype = Epic', 'is epic of')")

Let’s break down the query to understand what it is doing behind the scenes.

Query Role
 (project = IDP AND issuetype = Epic)  To get the list of epics
 issueFunction in linkedIssuesOf(“project = IDP AND issuetype = Epic”, “is epic of”)  To get the stories under those epics
 issueFunction in subtasksOf(“issueFunction in linkedIssuesOf(‘project = IDP AND issuetype = Epic’, ‘is epic of’)”)  To get the subtasks of those stories

Current User

The Current User context is used in very specific ways, it is not highly flexible so you need to know exactly when to use it.

The Current User variable can be used with the following fields/custom fields

  • Reporter
  • Assignee
  • Watcher
  • Voter
  • Any User Picker custom field

An example of use would be:

assignee = currentUser()

However, the following query, used to get issues where the current user is part of the Approvers multiple user field, is wrong and will generate an error:

currentUser() in “Approvers (CR)”

Even though it is a Multiple User picker, what you need to do instead is the following:

"Approvers (CR)" = currentUser()

User Membership

If you want to get the list of issues where the assignee or reporter or any User Picker custom field value is part of a specific group in Jira, you can use the built-in membersOf(GroupName) function.

assignee in membersOf("jira-administrators")

Portfolio and ScriptRunner for Jira

I find it quite interesting and useful that you can perform JQL queries according to your Jira Portfolio configuration. There are two apps that provide Jira Portfolio related JQL functions:

  • Portfolio for Jira
  • ScriptRunner for Jira

Depending on the complexity of the query you’d like to perform, you might or might not need ScriptRunner.

Portfolio for Jira

If you have Portfolio for Jira installed, you will be able to use the Parent Link custom field against committed changes.

Parent Link" in ("project=IDP")

This way, you get all the issues that have a parent (in Portfolio for Jira context) in project with key IDP.

ScriptRunner

In case you want  more advanced Jira Portfolio JQL functions, you can use ScriptRunner.

For these ScriptRunner JQL functions to appear, you will need to have Portfolio for Jira installed. Fair enough, right?

Also, make sure to have the right ScriptRunner version that has these functions, which is 5.0.11 onwards.

This app will add the following JQL functions to your instance :

JQL Function Description
portfolioChildrenOf(Subquery)

Finds children of the issues in the specified subquery.

By children, we obviously make reference to Portfolio for Jira configured hierarchy.

If you want to check your current hierarchy, do the following:

  1. Login as an administrator to Jira
  2. Go to > Add-ons
  3. In the PORTFOLIO FOR Jira section, click on Portfolio Hierarchy Configuration link
portfolioParentsOf(Subquery) Finds parent issues of the provided subquery.

Again, by Parent, we make reference to the Portfolio for Jira configured hierarchy.

Example

Assuming you have the following issue hierarchy configured in Portfolio for Jira:

→ Initiative

→ Epic

→ Story

If you want to get all Epics under initiatives that are in a specific status, you will need to run the following query:

issueFunction in portfolioChildrenOf("status='To Do'") and issuetype=Epic

In case you would like to add stories to the previous query, you will need to amend the query as follows:


(issueFunction in portfolioChildrenOf("status='To Do'") and issuetype=Epic) OR (issueFunction in linkedIssuesOf("issueFunction in portfolioChildrenOf('status=\\'To Do\\'') and issuetype=Epic", "is epic of"))

Because it is a nested query, things can get really confusing, so you need to make sure to escape the quotes, which is what we did for the \\’To Do\\’ bit

If you want to get all initiatives related to a specific set of epics, you will need to run the following query:

issueFunction in portfolioParentsOf("issuetype=Epic and project=FL") and issuetype=Initiative
For all the portfolio related JQL queries, beware that it will only work against committed changes in your Portfolio plan.

Jira JQL: thinking inside and outside the box

There is already a lot that you can do with the out of the box features, but apps are always a plus to help you create more targeted and specific queries.

I hope this article has been useful for you, but if you still have questions on how to set up your JQL queries don’t hesitate to get in touch with an Atlassian certified consultant at Valiantys!