Jira Expression of the week — Data Aggregation

Photo by Adeolu Eletu on Unsplash

So far, we mainly concentrated on using Jira Expression to build custom validators or conditions for fine-grained workflow control. But Jira Expressions can do more than that. Today, we will do some data aggregation with Jira Expressions in a real-world scenario. In fact, just like last week’s Feature Freeze, this is something we are using ourselves here at Jodocus.

There is always a lot to do at a startup and it is sometimes hard to keep up with who does what. Using Jira helps a lot, but we also wanted to have something delivered to our inbox at regular intervals. And so the idea for our weekly e-mail report was born which — among other things — helps us understand how work is distributed among the team, e.g. if there is someone who has a disproportionate amount of issues to their name. This information can easily be retrieved using a Jira Expression: This week’s Jira Expression of the week.

How is work distributed among the team?

OK, we want to know how issues are distributed among the team. We are not interested in work that’s already finished, so we do not care about closed issue. Also, we do not care about unassigned issues; they only become relevant for this report once they have been assigned to a team member. Getting a list of these issues is actually pretty easy with a short Jira Query Language (JQL) statement:

assignee != null and status != Closed

But instead of a long list of issues, we want something like this:

Distribution of assigned Issues

Basically, what we need for our graphic is a so-called map of aggregated data. A map is a data structure that stores key-value pairs. In this example, the name serves as a key, while the number of issues assigned to a person is the value. It would look something like this:

“value”: {
  “John Doe”: 14.0,
  “Jane Doe”: 22.0,
  “Humphrey Smith”: 40.0,
  “Oliver Siebenmarck”: 47.0,
  “Dogbert”: 4.0
 }

To get these numbers, we will use Jira Cloud’s REST API which conveniently has an endpoint for Jira Expressions, allowing us to execute a Jira Expression in the context of a list of issues, which is defined by a JQL query. Exactly what we need!

You do not need any extra apps to follow along — everything can be done via the Jira Expression REST API. However, the easiest way to work with Jira Expressions is to use our free app, Expression Tester. You can install the Expression Tester from the Atlassian Marketplace.

This week’s expression is a bit on the longer side, but don’t worry, we will take it step by step:

issues.reduce(
 (result, issue) => 
    result.set( 
      issue.assignee.displayName, 
     (result[issue.assignee.displayName] || 0) + 1)
  , new Map())

When we use the REST API with a JQL query, Jira will place the resulting issues in a list and make that available to our expression as issues. That list contains a lot more data than we want, so we need to reduce it a bit. Jira Expressions allow us to do that with the reduce()-function. In this example, it takes two arguments: a function to apply to all issues and a map of the results.
The map is easy to spot, that’s the new Map() part in the last line. The function definition is pretty much the remainder of the expression, here again for clarity:

(result, issue) => 
  result.set( 
   issue.assignee.displayName, 
   (result[issue.assignee.displayName] || 0) + 1)

Jira will now run this function for every issue in the list of issues. When it is run for the first issue result will be the newly created (and thus empty) Map, but we will add data to it for every issue. That is what the set()-function does. It takes a key-value pair and adds it to a map. If the key already exists, its value is overwritten with the new value.
As the key we use the assignee’s name, that’s pretty straight forward. Getting the correct value is a bit more elaborate: Every time we run set() for a specific user’s name, we want to add one to the value that’s already listed there. But how? Well, with result[issue.assignee.displayName] we get the current number to which we add one with + 1. Of course, this will not work the first time we count an issue for a user: There is no previous value. That’s the what the || 0 part of (result[issue.assignee.displayName] || 0) helps us with. As you might have guessed|| is a logical OR. Here, it basically says: either use the number from result[issue.assignee.displayName] or, failing that, use zero.

And that’s already everything there is to it. When sending this week’s expression to the REST API, this is the result:

{
    "meta": {
        "issues": {
            "jql": {
                "count": 127,
                "maxResults": 1000,
                "startAt": 0,
                "totalCount": 127
            }
        }
    },
    "value": {
       “John Doe”: 14.0,
       “Jane Doe”: 22.0,
       “Humphrey Smith”: 40.0,
       “Oliver Siebenmarck”: 47.0,
       “Dogbert”: 4.0
    }
}

Exactly what we set out to retrieve. At Jodocus, we use this data to build the pie chart from above, but of course any other visualization is possible, such as this bar chart:

Bar chart of assigned issues

As you can see, aggregating data with Jira Expressions is rather easy, can be super-useful, and yields great results with very little effort. Do you use Jira Expression in your organization? Let us know! And come back next week, when we take a look at how to work with custom fields.