Query parent and child issues from Jira

Still need help?

The Atlassian Community is here for you.

Ask the community

In Jira, it is common to create issues that have a parent and child relationship. For example, you may have an epic with child tasks, or a story with child subtasks.

You can use Atlassian Analytics to query an issue and its related parent or child issues. Let’s walk through how this can be done to create a chart that finds all the child issues under a parent issue.

Querying child issues with Atlassian Analytics

The “Issue” table contains information about your Jira issues. The parent of a specific issue can be found by querying the “Parent issue ID” column, which is the ID of the Jira issue’s parent issue.

If you don't see the "Parent issue ID" column, it may be because your Atlassian Data Lake connection was created before its release in October 2023. To get the new column, ask your organization admin to edit the connection.

In this example, we’re creating a chart listing the child issues of an epic.

  1. Open the chart editor by creating a new chart or editing an existing chart.

  2. In your visual mode query, add the Issue ID, Issue key, and Issue type columns from the Issue table under “Jira family of products”. Change the aggregation of all columns to Group.

  3. Add a filter where the Issue key column from the Issue table equals the key of the parent issue you want data for.

  4. Add a second query by selecting Add query and choosing New query.

  5. In the new visual mode query, add the Parent issue ID, Issue key, and Issue type columns from the Issue table under “Jira family of products”.

  6. Add a filter where the Parent issue ID from the Issue table is not null. This returns the values of the parent issues for Jira issues that have a parent issue, with “Issue key” being the issue keys of the child issues. Select Run query.

  7. Select the “Join” step and change the join type from “Outer” to “Inner”. Doing an inner join filters the results from Query 2 so that only child issues that have a “Parent Issue ID” equal to the “Issue ID” of the selected epic in Query 1 are returned.

  8. Hide the “Issue ID” column if it is no longer needed.

  9. Rename the “Issue key” and “Issue key:1” columns to “Parent issue” and “Child issue” to specify what the columns are, and similarly with the “Issue type” and “Issue type:1” columns.

  10. Now you have a chart displaying all the child issues that belong to a parent issue.

Querying three levels of hierarchy

You may have a scenario where you want to query issues and see more than two levels of issue hierarchy from Jira Software. For instance, from epic, task, to subtask, or initiative, epic, to story. This is possible by following the same approach used above, using the “Parent issue ID” column to find the parent of an issue.

Continuing from the above example, we want to see corresponding subtasks that fall under the stories of the selected epic.

  1. Open the query editor of Query 2 from the chart above. Add the Issue ID column from the Issue table under “Jira family of products”. Change the aggregation of the column to Group.

  2. After the “Join” step, reorder the columns so the new issue ID column, “Issue ID:1”, is the very leftmost column. This enables the stories' issue ID to be used to join with the child subtask issues.

  3. Add a third query by selecting Add query and choosing Copy of Query 2.

  4. Remove the Issue ID column from this new query, as this is not needed. Select Run query.

  5. Select the new “Join” step and change the join type from “Outer” to “Left”. Doing a left join filters the results from the 3rd query so that only child issues that have a “Parent Issue ID” equal to the “Issue ID” of the stories in Query 2 are returned.

  6. Optionally, hide the “Issue ID:1” and issue type columns and rename the remaining columns for clarity.

  7. Now we have a chart that shows the hierarchy of issues under an epic and its stories.

Filter by epic using a “Dropdown” control

To make the chart more dynamic, we can add a “Dropdown” control to filter by a selected epic(s).

  1. Add a “Dropdown control” from your dashboard and select Switch to control editor.

  2. In your visual mode query, add the Issue key column from the Issue table under “Jira family of products”.

  3. Add a filter for Issue type from the Issue table under “Jira family of products” equals 'Epic'. This query returns the issue keys of your epics.

  4. Optionally, rename the column to “Epic issue key” to provide clarity.

  5. Configure the settings of your “Dropdown” control and save it to your dashboard.

  6. Using the chart we previously created as an example, connect the “Dropdown” control to the chart by modifying the filter in Query 1 to “Issue key” is one of {EPIC}. Save the chart to your dashboard.

  7. Now you can dynamically filter the chart to display the issues under your selected epics.


Last modified on Nov 20, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.