Complex Jira Query help

[Phillip_Dang]

Im trying to create a filter that I can use in "Plans". The filter would query four different levels in my hierarchy. across two jira projects

Using the legend below I want to return all the Level 1,2,3,4 jira issues that are children (grand children and great grand children)  of a Level 1 key

The resultset would include

• The LVL1 jira ticket that is the parent of the LVL2 tickets below (should only be 1 jira ticket)
• All LVL2 jira tickets that are parents of the LVL3 tickets below
• All LVL3 jira tickets that are parents of the LVL4 tickets below (i.e. the epics)
• All LVL4 jira tickets where the fix_version = X.X

Hierarchy:

• Level 1 (LVL1) (issue type: "Group" in Project A) 
• Level 2 (LVL2) (issue type: "Deliverable" in Project A)
• Level 3 (LVL3) (issue type: "Epic" in Project B)
• Level 4 (LVL4) (issue type: various in Project B) but all have attribute fix_version

I can get all of Level 3 and 4, that is easy. And I can get Level 1 because it is only one jira issue and I know what that is.  The issue is I don't know how to get the correct LVL2 tickets.  Is there a grandparent function that can be used?

[Trudy Claspill · Accepted solution]

Hello @Phillip_Dang 

If you have the key of the highest level issue, and you have created the hierarchy structure in the settings for Issue Type Hierarchy, then you should be able to use the portfolioChildIssuesOf() function with the level 1 issue key to get all the issues that are under it. Refer to

https://support.atlassian.com/jira-software-cloud/docs/search-for-advanced-roadmaps-custom-fields-in-jql/#Child-issues

[Phillip_Dang]

Thank you Trudy.  

The challenge I have with this approach is that I do not want ALL the children of Level 1.  I only want the children of Level 1 that are parents of Level 3.  And I only want the Level 2 issues that are the parents of Level 3 that have children of Level 4 that meet the fix version criteria.

Retrieving Level 4 and Level 3 are easy, but finding the grandparents of Level 4 (i.e. Level 2 of the hierarchy that are parents of Level 3 that are parents of Level 4 is the tough part.

[Trudy Claspill]

Oh, I see! I did not pay close enough attention when I read your post. 

There is not a native "hasChildren" type of field that you can use in JQL to get only issues that actually have child issues.

There may be a method to get the results you want with a third party app that extends JQL capabilities. Do you have any such apps or are you willing to consider acquiring one?