JQL to find all issues where issuetype is a Sub-task and parent is an epic

[Cash Coyne]

I need a JQL query to find all issues where issuetype is a Sub-task and parent is an epic.

This doesn't work, but it expresses the idea:

issuetype = Sub-task and parent in (issuetype = epic)

Any suggestions?

[Cash Coyne · Accepted solution]

I ended up using 2 ScriptRunner filters:

First one, called "Child issues of Epics - ScriptRunner":

issueFunction in issuesInEpics("project=ABC and issuetype = epic")

Then using that ScriptRunner filter, created this one:

issueFunction in subtasksOf("Filter='Child issues of Epics - ScriptRunner'")

It gave me the results I was looking for.

[Rebekka Heilmann (viadee)]

Good job and thanks for letting us know! :) 

[Hannes Obweger - JXL for Jira]

Hi @Cash Coyne

Unfortunately, this is trickier than one might think; as a hierarchical query, it would really require some kind of "join" or "subquery", which isn't available in plain Jira/JQL.

A few directions forward:

If it's a one-off thing, you could first query the relevant epics, and then use the keys of these epics in a second query, in an "parent in (KEY-1, KEY-2, ...)" clause.

If you want to run your search dynamically, without manually "stitching" two queries together, you'll need extra tooling. You can obviously export your data and do some kind of post-processing, but there's also a range of apps from the Atlassian Marketplace that can help with that. These apps would either extend JQL by additional functions - Scriptrunner and JQL Search Extensions are popular, but there are others, too - or provide there own, non-JQL search and filter mechanisms. I myself happen to work on an app that does so, and would allow solving your use case in just a couple of clicks; I'll provide more information below.

Hope this helps,

Best,

Hannes

[Hannes Obweger - JXL for Jira]

Just to expand on the last point, this is how this would look in the app that my team and I are working on, JXL for Jira:

For context, JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a number of so-called smart columns that aren’t natively available, including the parent issue type.

As you can see above, you can easily sort and filter by the parent issue type, and also use it across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

Any questions just let me know!

[Oliver Siebenmarck _Polymetis Apps_]

Hey @Cash Coyne ,

I don't have a solution in straight up JQL. But if you need this for an app or a script or somewhere else where you are flexible, you should be able to get this with Jira Expressions.

Jira Expressions allow you to send along a JQL query and then run them on a list of the results. So in your case, you would send the following JQL:

issuetype = Sub-task

as well as this expression:

issues.filter(i => i.parent.issueType.name == "Epic")

Which will give you a JSON list of those sub-tasks that have an Epic as their parent. I know, this is not ideal, but it should get you the results you are looking for. You can use Jira Expressions via their native API or with our free app Expression Tester. 

Hope that helps,
 Oliver

[Rebekka Heilmann (viadee)]

Hi @Cash Coyne 

JQL doesn't allow for Sub-Queries so not sure if there is a solution without the use of Marketplace apps.

Regardless, there can't be issues that are Sub-Tasks and also have an Epic as parent, assuming you still use the default hierarchy which is Epic - Task - Sub-Task.

Only Tasks (or other Level 0 Issue Types) can be the Child of a Parent. And Sub-Tasks can only be the Child of Level 0 Issue Types.

So even if the filter was working, there wouldn't be any results. 

[Matt Parks]

I'm not sure if Cloud is different, but in Data Center it is definitely possible for a sub-task issue type to have any non-sub-task issue type as its parent, regardless of where the parent is in the hierarchy.

We even have sub-tasks on Initiatives (the parent issue type for an Epic).

[Hannes Obweger - JXL for Jira]

Also very much possible in Cloud. The option to create a sub-task below an epic is somewhat hidden in the UI, but once you find a way to do it (and be it via the API) it works just fine.

[Rebekka Heilmann (viadee)]

Interesting. Always learning something on here :)