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.

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

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

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

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

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

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

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

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

Nested queries 

You can use the new JQL keywords together, for instance 

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

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