Is there a way to write tree or hierarchical JQL queries in Jira?

[Kshitij Chaudhari]

Firstly, does Jira support using filters with result shown as tree-based result set i.e. a list of epics that were searched for and their child stories listed below them or similar queries?
If so, what is a good approach or info to write such queries; for example, I want to find specific epics that were once in Discovery status and currently are in Open or In Progress status with all child stories shown as tree structure or parent child hierarchy results.
I'd also like to additional filter the stories under the parent by date they were created and their labels as well as current status.
Is this possible using JQL today?

[Trudy Claspill · Accepted solution]

Hello @Kshitij Chaudhari 

Welcome to the Atlassian community.

The answer to your first question is no, that is not a capability available natively.

Regarding your query, you are looking for a capability to get a list of child is of Epics derived from the results of another query 

That is not available natively. It would require a third party app.

You can find information about the JQL functions that are available natively here:

https://support.atlassian.com/jira-software-cloud/docs/jql-functions/

How often do you need to execute this query? Is it a one time need or an ongoing need?

Does your Jira instance already have any third party apps that extend JQL capabilities? ScriptRunner Enhanced Search is one example.

[Ivan Manolov - JXL]

Hello @Kshitij Chaudhari

Welcome to the community.

As mentioned by @Trudy Claspill , there's no native capability in Jira to show a tree-based result set using JQL.

For an improved visual experience, and if you’re open to solutions from the Atlassian Marketplace, you may want to have a look at the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your Jira data that allows viewing, inline-editing, copy-pasting, sorting, and filtering by all your work items' fields, much like you’d do in e.g. Excel, Google Sheets, Smartsheet, or Airtable. It also comes with a number of powerful features, including highly customisable issue hierarchies. These issue hierarchies can be based on Jira's built-in parent/child relationships (like task/sub-task, or epic/story), and based on issue links of configurable issue link types. 

This is how this looks in action:

You can simply load your relevant issues into a sheet, enable the default issue hierarchy (that's just one click), and then filter down to the epics and stories that you care about.

Any questions just let me know.

Best,
Ivan

[Rahul_RVS]

Hi @Kshitij Chaudhari 

Welcome to the community !!

As suggested by Trudy a mktplace app can help here. If you would be interested in a solution to view linked issues in a tree  / hierarchy view, I can suggest our app

Issue Hierarchy 

The app allows you to view your Epic Hierarchy in a tree view. You can view %complete progress at each parent level. It rolls up the time tracking fields, story point or numeric fields at each parent level. The app can be added to a dashboard as well.

What's more, you can also filter your child issues below Epics with customized JQL as per your requirement. Do give it a try.

Disclaimer : I am one of the app team member

[Kshitij Chaudhari]

No native capability without adding marketplace extensions.

[Thiago Wenceslau de Santana]

Hi  

I’m Thiago, a support engineer at Appfire and I’m here to help you.

If you are considering using third-party apps, the Jira Search Extension (JQLSE) would be the fit for your needs.

With JQLSE, you can nest search your own queries, as well as saved filters. It is also possible to search for children of epics recursively through childrenOfIssuesInQuery() function. Below is an example of query based on what you’ve shared:

 project='YourProject and type=Epic and status in (Open, 'In Progress') OR issue in childrenOfIssuesInQuery("
project='YourProject' and type=Epic and status in (Open, 'In Progress') and created > -7d
") ORDER BY type ASC

Currently it isn't possible to check for past issue status.

Remember to set the conditions accordingly to your needs.

Please contact our support if you have any other questions about this.

Best regards, Appfire support team.

[Danut M _StonikByte_]

Hi @Kshitij Chaudhari,

Welcome to the Atlassian Community.

Yes, there is a way to see the issues hierarchically in Jira. This can be done on the List view of every Jira project/space. 

It offers also some filtering/SQL options when clicking Filter.

To filter your epics, you can use a JQL like this:

issueType = Epic AND status WAS Discovery AND status IN (Open, "In Progress") 

Unfortunately you cannot include the stories along with these epics, unless you use a SQL that filters by epic keys. This can work if you don't have some many epics.  

(issueType = Epic AND status WAS Discovery AND status IN (Open, "In Progress")) OR parent IN (EPIC-1, EPIC2, EPIC-3...)

Another possibility would be to try using an app from Atlassian Marketplace. If you are willing to try using an app, our Great Gadgets app offers a Work Breakdown Structure (WBS) gadget that can display the issues from a filter/JQL by their hierarchy Epic > Stories > Sub-tasks. 

You can achieve what you want if you configure it with a JQL like the one above, or like this one:   

(issueType = Epic AND status WAS Discovery AND status IN (Open, "In Progress")) OR (issueType = Story AND project IN (ProjectA, Project-B))

Hope this helps.

Danut.

[Trudy Claspill]

@Danut M _StonikByte_ 

While the List view does provide a hierarchical view, you cannot apply a custom JQL to it to get the specified results. The Filter button doesn't support using custom JQL.

[Danut M _StonikByte_]

Hi @Trudy Claspill,

The JQL option is visible to me.  

Can you please double-check?

Danut 

[Trudy Claspill]

Interesting!

That feature doesn't appear to be available in my Free instance. I looked through the entire list of options that displayed and did not see one for getting to a JQL entry viw.

[Danut M _StonikByte_]

Hi @Trudy Claspill 

It could be one of those features Atlassian tests on selected instances before rolling them out globally. I see no other explanation...

I also hadn’t realized it only occurs on one of my instances. I checked another instance, and indeed, it doesn’t appear there.

Anyway, this feature is expected to come soon :)

Danut