Create custom JQL functions for nested issue reporting

Problem

You need to search for issues across multiple hierarchy levels (epics, stories, and subtasks) in a single query. Standard JQL cannot easily retrieve all related issues following the complete hierarchy structure from epics down to their stories and subtasks.

Solution

Create a custom SIL script that traverses the issue hierarchy by finding all epics in a project, then retrieving all linked stories for each epic, and finally collecting all subtasks for each story. The script combines all results into a single array that can be used in JQL queries.

Contents: 1 Problem 2 Solution 2.1 Script 3 Implementation steps 4 Example usage

Contents: 1 Problem 2 Solution 2.1 Script 3 Implementation steps 4 Example usage

See the step-by-step coding process in this detailed walkthrough:

Script

string [] tasks;
string [] stories;
string [] epics = selectIssues("issueType = 'Epic' AND project = " + argv[0]);
for(string e in epics) {
    string [] stry = allLinkedIssues(e, "Epic-Story Link");
    for(string s in stry) {
        string [] tsk = allLinkedIssues(s, "jira_subtask_link", 1);
        tasks += tsk;
    }
    stories += stry;
}
string [] results;
results = arrayUnion(epics, stories);
results = arrayUnion(results, tasks);
return results;

Implementation steps

Create the SIL script using the code provided above.
Customize the script parameters if needed:
- Modify the epic selection criteria in the selectIssues function.
- Adjust link types if your project uses different linking relationships.
Save the script as jqlnestedsearch.sil (or your preferred name).
Use the script in JQL queries as shown in the example below.

Example usage

This JQL query returns all epics, stories, and subtasks from project TEST following the complete hierarchy structure.

key in silJQLList("jqlnestedsearch.sil", "TEST")

You can combine this with additional JQL filters:

key in silJQLList("jqlnestedsearch.sil", "TEST") AND created >= -7d

This second example returns the same hierarchical results, but only for issues created in the last seven days.