--- title: "Subqueries - Jira Cloud" canonical: "https://support.appfire.com/space/JQLSEARCH/604209666/Subqueries%20-%20Jira%20Cloud" format: markdown --- > 📝 **Deprecation of Subqueries feature** > 📝 > 📝 From October 2023, the Subqueries feature is no longer available in JQL Search Extensions. This change affects customers with **saved JQL subqueries** who installed the app before** **April 2021. > 📝 > 📝 The 6-month deprecation plan was communicated to these customers with recommendations to convert any subqueries through [Extended Search](https://appfire.atlassian.net/wiki/spaces/JQLSEARCH/pages/604209464) before the feature was deprecated. Extended Search provides an equivalent function for every subquery JQL keyword with better performance and reliability. # Summary Subqueries allow you to create complex JQL queries that normally require creating multiple queries 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 like the rest of the JQL Search Extensions keywords. This means they integrate well with advanced search, filters, gadgets, and other Jira components that use standard JQL. > Macro (toc) # 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**: ```sql 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 just to add a subtaskPriority clause: ```sql issueType="Story" AND subtaskStatus!="Done" AND subtaskPriority="High" ``` However, this query matches any stories with a **subtask** that is **not done** and a **subtask** that is **high priority**; it doesn't need to be the same subtask. The solution is to create a base query that finds all the **subtasks** that are **not done** and of a **high priority**: ```sql status!="Done" AND priority="High" ``` Then, find the stories that are parents of issues matched by the base query: ```sql type="Story" AND parentOfQuery="status!=\"Done\" AND priority=\"High\"" ``` ***parentOfQuery*** is one of the keywords that can be used with subqueries. > ℹ️ You need to create your base subquery first to be able to use it with subquery keywords. The process is documented below. --- # Limits This functionality allows for JQL queries that return a **maximum of 2000 issues**. Keep in mind that the results might be outdated for a very short period of time. If you encounter any issues, send feedback or feature requests through our [support portal](https://appf.re/support).  --- # Getting Started The subquery screen is accessible from the left-hand project menu and the general menu (or the top menu in old Jira UI). ![image](media://9576e320-d2bd-4ace-90f7-b8251638d1f8) ## Creating a subquery Click the **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. ![image](media://1857cb61-b404-4ea4-afc8-af59e93fe5e7) 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 to form a correct query. After pressing Enter, the query is verified, and in a few seconds, a preview of the results shows up below. ![image](media://511b78de-4fe4-4750-91ca-b9929e30083b) If you're happy with the results, you can optionally assign a meaningful keyword for the query. To proceed with the creation of the subquery, click the **Index Subquery** button. ![image](media://d5365f06-9816-41e8-8851-1dac87112f12) 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 keywords 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. ![image](media://1b0637f7-af75-4b54-adc4-e01343c18e6b) --- # 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: ```sql createdDate >= "2017-11-01" and createdDate <= "2017-11-30" ``` Optionally assign some keyword like **"created in November"** and then use the new JQL keyword **subtaskOfQuery** to find the results: ```sql subtaskOfQuery="created in November" ``` The same query without keyword will need to escape the quotes. It shouldn't be problematic though as Jira will aid you with autocompletions: ```sql 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: ```sql 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: ```sql 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** | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | :check_mark: | :check_mark: | :cross_mark: | :cross_mark: | :cross_mark: | :cross_mark: | :cross_mark: | :cross_mark: | :check_mark: | :check_mark: | :check_mark: | :check_mark: | :cross_mark: | :cross_mark: | :cross_mark: | :cross_mark: | :cross_mark: | > ℹ️ ### **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 keywords: | **keyword** | **Query** | | --- | --- | | Team 8 this week | labels in (Team8, Marketing) and createdDate > -7d | | Abc High | project=ABC and priority=High | > ℹ️ And then you can conveniently refer to the queries with their keywords 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. ![image](media://1d7f7a56-8655-4067-ba53-92e4f419b524) 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](https://docs.oracle.com/javase/8/docs/api/java/util/regex/Pattern.html)). 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 **keyword** for convenient use, otherwise a descriptive keyword will be autogenerated from the JQL, field and regular expression parameters. 4. Click on the Index field match button to begin the indexing process. ![image](media://7bd20343-d810-4fc7-b807-5be43d7dbe27) ## Using field match queries After the field match query is indexed the results are available under the fieldMatch JQL keyword. For example if the keyword 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: ![image](media://3ec08910-5b8e-4f7b-a88f-fcfa75a24d33) --- # 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()