Search code examples
jirajql

How To Query Epics With All Closed Jira issue?

Using JQL and Adaptavist ScriptRunner, how can I write a query that returns - for my project "ABC" - epics that have only closed Jira associated issues?

Solution

  • Introduction

    Let's construct this query using set theory. Part One will build up the query that returns everything we don't want. Part Two will build the query of everything. Finally we will combine the two to return what we do want. Onto part one!...

    Part One: Project Epics With Open Associated Jira Issues

    First, let's return the Jira issues that are associated with an ABC epic:

    issueFunction in issuesInEpics("project = ABC")

    Expanding that query, we prefix with a clause that will return the epics. That is, the epics of the Jira issues that are associated with an ABC epic:

    issueFunction in epicsOf("issueFunction in issuesInEpics(\"project = ABC\")")

    Thus far, we do not query on status. However recall that we want only epics with all closed Jira issues. The next clause may seem counterintuitive because here we will add a statusCategory check for Jira issues that are not done:

    issueFunction in epicsOf("issueFunction in issuesInEpics(\"project = ABC\") and statusCategory != Done")

    So, the above says please give me the epics associated with Jira issues that have yet to be done. That is for Jira issues that have an ABC project epic associated.

    Let's leave that query for now and start a new one.

    Part Two: Open Project Epics With Associated Jira Issues

    Same query as above. All Jira issues that have an ABC epic associated:

    issueFunction in issuesInEpics("project = ABC")

    and again, same as above:

    issueFunction in epicsOf("issueFunction in issuesInEpics(\"project = ABC\")")

    Now, some variation. We don't care about closed epics. This clause applies specifically to the epics, not to the associated Jira issues...

    statusCategory != Done AND issueFunction in epicsOf("issueFunction in issuesInEpics(\"project = ABC\")")

    Combining Part One and Part Two

    Great! We now have our list of open ABC epics that have associated Jira issues. Let's combine both to give us the difference between part one and part two. That is, open ABC epics that are not in the set of ABC epics with an open associated Jira issue:

    statusCategory != Done AND issueFunction in epicsOf("issueFunction in issuesInEpics(\"project = ABC\")") AND NOT issueFunction in epicsOf("issueFunction in issuesInEpics(\"project = ABC\") and statusCategory != Done")

    Opening some of the epics returned, one can see that they are open yet have all associated Jira issues closed. Problem solved!

    Note that this could be made tidier by using sub filters. For more info on that, checkout the examples on the ScriptRunner reference.