Like in math class, functions in Jira boil down complex logic and make it easy for you to access and use in a simple way. Here’s how they work in JQL.
You are now on your way to creating more effective queries in Jira software to have a clearer view of your projects, teams, and work ahead. If you’ve not seen lesson 1, I’d suggest reviewing that post as we will build on a number of concepts talked about in that post. You should be comfortable with simple JQL queries as well as using the AND and OR keywords at this point.
Functions: What do they do?
Like in math class, functions boil down complex logic and make it easy for you to access and use in a simple way.
For example, Jira supports a function called membersof() that you can use to see all the issues assigned to members of a group. Groups can be defined inside of Jira or come from existing groups in your company’s preexisting directory servers.
project = Pipeline AND assignee in membersof(‘test-engineering’)
This query returns all of the issues that are assigned to test engineers. Functions also react dynamically as the environment changes. If more users are added to the test-engineering group, this query will dynamically update.
Let’s see how we might see what issues got fixed in the last release.
project = Pipeline AND status in (resolved, closed) and fixversion = “Sprint A”
This will return all of the issues that were fixed in that particular release. What if we want a list that always shows the issues fixed in the last release whenever it was run? That’s very easy to do with a function.
status in (resolved, closed) and fixVersion = latestReleasedVersion(Pipeline)
What’s the difference here? Eventually, the team will release sprints B, C, and D. The first query only returns the list of issues resolved in Sprint A. The second one queries Jira to find the last released version for project Pipeline. Thus as sprints B, C, and D are released this query will pull the right data for those releases. Pretty cool, aye?
History: What happened back then?
You can also use JQL to see how our work has been done over time. For instance, if you wanted to see the bugs that John Smith resolved in the Pipeline project, you can use the following query:
project = Pipeline AND status CHANGED FROM “In Progress” TO “Resolved” BY jsmith
Sometimes issues that fail verification or get reopened are of special interest to see why a change didn’t work as intended. To find those bugs it’s easy to run this query:
status CHANGED FROM “In Progress” TO “Open”
To see what issues were in flight during the current week we can use the following JQL:
status was (“In Progress”) DURING (startofweek(), endofweek());
At the end of the year it can be nice to see how many issues you resolved during the year. To do so, all it takes is a little JQL:
resolution changed to “Fixed” by currentUser()
during (startOfYear(), endOfYear())
Scoping and Sorting: Your new secret weapon
Oftentimes when you start searching your Jira issues, you can come back with A LOT of information. Scoping and sorting your queries will make it easy to see the exact information you need.
- Scoping – focusing your query so it pulls the right amount of data so the user sees only the information relevant to the current item at hand
- Sorting – ordering your data such that the most critical set of data is listed first
Scoping
Starting with open issues:
project = Pipeline AND status = open
You might find that query returns too many issues as it includes everything from your backlog as well as issues you are currently working on. You can tighten it up a bit by eliminating the backlog issues.
project = Pipeline AND status = open AND fixVersion = “Current Sprint”
This result is better, but you can narrow it down even more. Let’s see what didn’t make last sprint and got moved into this sprint.
project = Pipeline AND status = open AND fixVersion = “Current Sprint”
AND fixVersion WAS “Last Sprint”
Now that you have the issues you really care about in front of you, it’s easy to look at each of these issues with a high degree of focus and see why they didn’t make the last sprint. Was it an estimation problem, requirements, or something else? The issues were there in the first query, but they were too hard to find manually. Good query creation is an iterative process: query, review, see if you can focus the results. The more you do, the easier it becomes.
Sorting
Let’s say you want to see all the bugs open for the team in a particular sprint:
project = Pipeline AND fixVersion = “Current Sprint” AND status = open
What are you trying to glean from this data? If you are trying to understand risk you might want to see the list ordered by priority and then by an engineer so that you can easily see if one engineer has two high-priority bugs. JQL has a keyword ORDER BY that tells Jira to sort the results. The above query could be extended to include sorting:
project = Pipeline AND fixVersion = “Current Sprint” AND status = open ORDER BY priority, assignee
Jira will first order the list by priority and then sort by assignee for all of the issues with the same priority. Let’s look at another example examining the incoming bugs to our project. We want to see any new critical or blocking bugs that have come in recently to see if recent checkins have decreased stability.
project = Pipeline AND priority in (blocker, critical) AND created > -2w ORDER BY created DESC
The query controls for priority and limiting the created time properly scope the query. What the sorting does is show us the most recent issues first. Use the DESC keyword to sort in reverse (newest to oldest). That way you focus your audience on the most important issues first. Not everyone will look at each issue in your reports. Always sort your queries so that the most important issues show up first.
In closing
At this point you should be feeling pretty good about querying Jira to pull issues with JQL. We’ve covered basic structure, functions, current data, historical data, sorting and scoping. If you’d like to explore more JQL, take a look at the JQL Reference. Here are some great JQL resources to keep reading:
And, to get even more JQL training, check out the product guide.