Subqueries - Jira Cloud

New JQL Search Extensions installations don't have access to Subqueries. You should use Extended Search instead as it is a replacement for Subqueries. Follow the migration guide to convert your subqueries to Extended Search filters.

Subqueries are currently only available on Jira Cloud. On Server, there are JQL Search Extensions functions that accept the JQL as an argument out of the box.

Summary

Subqueries allow you to create complex JQL queries that normally require creating more than one query and possibly some manual copy and paste operations.

  • They can free you from Excel for a number of tasks.
  • They are easy to use thanks to support for autocompletion.

Subqueries can be used in native JQL just like the rest of JQL Search Extensions keywords. This means they integrate well with advanced search, filters, gadgets and any other Jira components that use standard JQL.

Why use Subqueries?

Subqueries are useful if you need to apply more criteria to your base query and then find all the subtasks, links, epics etc that are related to the base query.


Consider the following example:

You may want to find stories with subtasks that are not done:

issueType="Story" AND subtaskStatus!="Done"


What if you want to find stories with high priority subtasks that are not done? You may think that it's enough to just add a subtaskPriority clause:

issueType="Story" AND subtaskStatus!="Done" AND subtaskPriority="High"

but this query matches any stories that have a subtask that is not done and a subtasks that is high priority - it doesn't need to be the same subtask!


The solution to this problem is to create a base query that finds all the subtasks that are not done and of a high priority:

status!="Done" AND priority="High"


Then, find the stories which are parents of issues matched by the base query:

type="Story" AND parentOfQuery="status!=\"Done\" AND priority=\"High\""

parentOfQuery is one of the aliases that can be used with subqueries.

You need to create your base subquery first to be able to use it with subquery aliases. The process is documented below.



Limits

This functionality allows for JQL queries that return a maximum of 2000 issues. Please keep in mind that the results might be outdated for a very short period of time. Please feel free to send any feedback or feature requests to support@digitaltoucan.com or raise through our service desk



Getting Started

The subquery screen is accessible from the left hand project menu as well as the general menu (or the top menu in old Jira UI).

Creating a subquery

Click on Create new subquery button to navigate to a form that allows creating new subqueries. It is the form you are presented with the first time you access the Subqueries screen.

You are navigated to the form that has a search box that allows you to create the subquery. While you type the JQL in the search box the standard autocompletion helps you out to form a correct query. After pressing Enter the query is verified and in a few seconds a preview of results shows up below.


If you're happy with the results you can optionally assign a meaningful alias for the query.

To proceed with the creation of the subquery click Index Subquery button.


If there are no errors you are moved to a list of subqueries where you can see the status of indexing. Initial indexing of the subquery will take some time depending on the number of issues involved.

Viewing list of subqueries

After creating your first subquery you will be presented with a list of available subqueries every time you access the subqueries screen from the menu.

Here you can see what subqueries are defined and the aliases associated with them.

You can also inspect the progress of subquery indexing. Once the indexing is complete you will see that the subquery is ready to use.



Subquery Examples

As a quick example you might want to search for parents of all the subtasks created in a particular date range, November 2017.

The way to do it with Subqueries screen is to create a new subquery for the date range:

createdDate >= "2017-11-01" and createdDate <= "2017-11-30"


Optionally assign some alias like "created in November" and then use the new JQL keyword subtaskOfQuery to find the results:

subtaskOfQuery="created in November"


The same query without alias will need to escape the quotes. It shouldn't be problematic though as Jira will aid you with autocompletions:

subtaskOfQuery="createdDate >= \"2017-11-01\" and createdDate <= \"2017-11-30\""


You can further refine your query to display only subtasks that have a particular label:

labels="Team 8" AND subtaskOfQuery="created in November"


As a bonus, the same subquery will be indexed for a few more keywords. It is possible for example to search for parents of issues created in November, epics of issues created in November etc. Below is a full list of Subquery keywords available.



Available JQL subquery keywords

The following feature descriptions are based on the example subquery that matches issues with labels Team8 and Marketing that were created in the last 7 days:

labels in (Team8, Marketing) and createdDate > -7d

This subquery needs to be first created in the Subqueries screen and indexed.

All the keywords support the following operations:

=

!=

~

!~

>

>=

<

<=

IS

IS NOT

IN

NOT IN

WAS

WAS IN

WAS NOT

WAS NOT IN

CHANGED

(tick)

(tick)

(error)

(error)

(error)

(error)

(error)

(error)

(tick)

(tick)

(tick)

(tick)

(error)

(error)

(error)

(error)

(error)


issuesInQuery

Matches issues returned by the base query itself. It can be used as a more powerful Jira filter.

For example you can create two subqueries with aliases:

AliasQuery
Team 8 this weeklabels in (Team8, Marketing) and createdDate > -7d
Abc Highproject=ABC and priority=High

And then you can conveniently refer to the queries with their aliases in more complex queries:

Example
issuesInQuery="Team 8 this week" and issuesInQuery="Abc High" and status!=Done

linkedByQuery

Matches issues linked by the issues returned by the subquery.

Example
linkedByQuery = "labels in (Team8, Marketing) and createdDate > -7d"

linksQuery

Matches issues that link to the issues returned by the subquery.

Example
linksQuery = "labels in (Team8, Marketing) and createdDate > -7d"

parentOfQuery

Matches parents of subtasks returned by the subquery.

Example
parentOfQuery = "labels in (Team8, Marketing) and createdDate > -7d"

subTaskOfQuery

Matches subtasks of issues returned by the subquery.

Example
subTaskOfQuery = "labels in (Team8, Marketing) and createdDate > -7d"

epicOfQuery

Matches epics of issues returned by the subquery.

Example
epicOfQuery = "labels in (Team8, Marketing) and createdDate > -7d"

issuesFromEpicsInQuery

Matches issues of the epics returned by the subquery.

Example
issuesFromEpicsInQuery = "labels in (Team8, Marketing) and createdDate > -7d"

Nested queries 

You can use the new JQL keywords together, for instance 

Example
linkedByQuery= "subTaskOfQuery = project = DEMO and issuetype = Bug"

The query above finds all issues which are linked by the subtasks of the bugs in the project demo. In order for this functionality to work, please first:

  1. Define a subquery: project = DEMO and issuetype = Bug
  2. Define another subquery that uses the first subquery: subTaskOfQuery = project = DEMO and issuetype = Bug

This will allow using subTaskOfQuery = project = DEMO and issuetype = Bug together with all the new JQL keywords. 



Field match

You can create a query for an issue field that matches a regular expression.

Creating new field match query

To create a new field match query go to JQL Subquery page and click on Create new field match button.


  1. In the field match creation screen provide the JQL for the issues that you want to limit your search to. Make sure the JQL returns less than 2000 issues.
  2. Next, choose a field and provide a regular expression that will be executed against the field value. Java regular expressions are supported (documentation). A non-strict version of regular expressions is used - to make the regular expression strict you need to surround it with the regex beginning (^) and end ($) characters.
  3. Make sure to give your query an alias for convenient use, otherwise a descriptive alias will be autogenerated from the JQL, field and regular expression parameters.
  4. Click on the Index field match button to begin the indexing process.

Using field match queries

After the field match query is indexed the results are available under the fieldMatch JQL keyword. For example if the alias for you query is "summary begins with Tesla":

Example
fieldMatch= "summary begins with Tesla"

Alternatively, you can quickly jump to the results by clicking on the Go to results link in the main subqueries screen:



Restrict Functionality in Global Permissions

The functionality is available to all the logged in users after installation.

If for some reason certain users shouldn't be allowed to access the screen and create subqueries there is a global permission that controls the access: JQL Search Extensions Subqueries creation.

‌‌


Unsupported JQL functions

Subquery will be executed using JQL Search Extensions credentials. This means that any JQL functions that rely on the current user context won't work or will return confusing results. The following JQL functions need to be avoided in subqueries:

  • currentLogin()
  • currentUser()
  • issueHistory()
  • lastLogin()
  • myApproval()
  • myPending()
  • projectsWhereUserHasPermission()
  • projectsWhereUserHasRole()
  • votedIssues()
  • watchedIssues()

The following functions are only allowed if you specify a user in the function parameters:

  • projectsLeadByUser()
  • componentsLeadByUser()