• Article
  • Oct.18.2018

Jira JQL functions: The ultimate guide

  • Oct.18.2018
  • Reading time mins

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.

If you type only the number (e.g. 146), you will be redirected to the issue with that number in the current project you are working on.

3 – Smart JQL querying

This type of search allows you to look for specific issues using short queries. There are a lot of keywords and tricks you can learn here to be able to optimize your time while looking for issues.

The most popular smart queries are:

Query
Result
Comments
my Returns all issues that are assigned to the current user
r:me Return all issues that are reported by the current user You can also put any username after r: (e.g. r:balkis.khouni)
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 used Jira Query Language and want a simple way to filter issues. It can be performed from the Issue Navigator.

To access the Issue Navigator: From the application header, choose Issues > Search for Issues.You will see the following search bar with some drop-downs:

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 the 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. Whenever you try 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 Jira 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 the 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)

 

The query above is equivalent to:

issueFunction in hasLinks("blocks") OR issueFunction in hasLinks("is blocked by")

 

The difference is:

  • hasLinkType take the issue link name as a parameter
  • hasLinks take the inward or outward link name as a parameter

When it comes to ScriptRunner JQL functions, we do not use issue object, we use issueFunction, which explains the query above. The following query will not work: issue in hasLinkType(Blocks)

If you want to filter all issues that have ANY linked issues, use hasLinks(link type) function:

issueFunction in hasLinks()

 

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 parentsOf("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 for Jira and ScriptRunner

I find it quite interesting and useful that you can perform JQL queries based on your Portfolio for Jira context. There are two apps that provide Jira Portfolio related JQL functions:

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

Using Portfolio for Jira only

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

To get all the issues that have a parent (in Portfolio for Jira context) that belong to the project with key IDP, run the following query:

"Parent Link" in ("project=IDP")

 

To get all the issues that have a parent (in Portfolio for Jira context) that belong to the project with key IDP, run the following query:

"Parent Link" in linkedIssues("TP-345") 

 

Using Portfolio for Jira and Scriptrunner

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

For these ScriptRunner JQL functions to appear, it is required you 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  > Apps
  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.

If you want more tips for Atlassian tools, get in touch with our team.

 

Get the most out of my Atlassian tools

Related resources

View all resources