How we use Jira Query Language in practice
My name is Sergey Rakov, I am the head of the B2G division at Rostelecom IT. I want to tell you about the Jira Query Language (JQL): how to use it in practice, basic techniques, what problems we faced and how we solved them.
Original picture taken from deviniti.com/atlassian
There are a lot of task trackers, each one is suitable for solving some problems and not very helpful in solving others. We used many of them, but now we settled on Jira – it is our main tool. Personally, I really like its JQL language, which greatly simplifies the work and allows you to have a powerful and flexible tool for finding tickets out of the box.
Out of the box, Jira has basic and advanced searches. These two search options can solve most of the problems facing the user. Basic search is familiar to the eye of any person who has used the services of online stores at least once – it works according to the same simple scheme. There are many filters: by projects, types of tasks, by executor and status. You can also add additional fields based on criteria supported by Jira.
But a problem arises when you need to go beyond basic queries. For example, if we want to find tasks that have ever been on a particular performer, or find all tasks, excluding one project. It is no longer possible to make a tricky selection for a project with one task status and executor and one more executor and another task status using basic search.
Advanced search comes to the rescue. JQL syntax is very similar to SQL. But in JQL, there is no need to select specific fields that we will select, indicate tables and databases from which we will output. We specify only a block with conditions and work with sorting – Jira automatically does the rest itself.
All you need to know to work with JQL is the names of the fields by which we will select tickets, the operators (=,! =, <,>, in, not in, was, is etc.), keywords (AND, OR, NOT, EMPTY, ORDER BY etc.) and functions that are available out of the box in advanced mode (Now (), CurrentUser (), IssueHistory (), EndOfDay () other).
Fields
Jira, when you type in the search bar, itself gives hints of all possible values that you are looking for: both by fields and by the values of these fields. For myself, I recently discovered an interesting systemic field lastViewed… Jira keeps track of your ticket views. 
Here are two options for composing filters for viewing recent tasks. The first is my option with lastViewedwhere Jira will return the issues I’ve viewed in the last seven days, sorted in descending order. This filter is set up on my dashboard as a gadget and I use it often. Because the ticket was closed, I did not remember the tab and the number, I quickly opened it, looked what the last ticket was.
There is a standard Viewed Recently filter. It uses the function IssueHistory (), sorting is also performed by the field lastViewed… The result is the same, but the method, even in Jira, can be used differently. It should be noted that the field LastViewed and IssueHistory () return only your browsing history – you won’t be able to view the history of third parties this way.
For the most part, Jira has standard operators. I like operators the most WAS, WAS IN, WAS NOT IN, WAS NOT, CHANGEDbecause they work with time. This is not possible in conventional databases.
Jira lets you work with historical data out of the box. Using the operator WAS you can find tickets where the performer was and is User1. If the ticket was on me, and then passed to someone else, the request will show that this ticket was once on me. It is clear that for a more detailed selection, you need to add some more conditions, but we will get to this later.
However, there is one caveat: Jira does not store history for text fields: ticket names and their descriptions. You cannot write there: “Show me tickets in which the Summary field contained the word “Rostelecom”“.
Second example with operator CHANGED… We want to receive tickets in which the artist was changed after January 1, 2020. Other additional words can be used, such as BEFORE or signs>, <, to whom it is more convenient, and a specific date. In the same example, you can also make a negation and see which tickets are hung on which users: assignee not changed AFTER ‘2020-01-01’…
Keywords
The main keywords are – OR, AND, NOT… They work the same way as logical operators. Using OR, we will get a full set of tickets from two projects A and B. If you need to narrow the selection, use AND… Example – we need tickets from project A, for which user B was the executor: project = A AND assignee = B… It’s the same with denial.
Functions
According to the documentation, there are 47 functions in Jira, but I’ve never used all of them. Here are a few, in my opinion, the main ones:
now () a popular feature that allows you to find tickets that, for example, have expired.
currentUser () returns the current user. Jira contains pre-configured filters that use this feature. FROM currentUser () you can make universal search queries. This is how I made a universal dashboard for the entire development team: I stuffed gadgets onto the dashboard and in each I indicated instead of a specific user currentUser ()… This dashboard will be unique for each logged in user, although the configuration will be the same.
unreleasedVersions () Is a function that returns tickets that are in unreleased versions. But it does not return tickets that do not have a version.
startOfDay () returns the beginning of the current day. There are functions for week, month and year. The same applies to the closing function endOfDay ()… They allow you to get rid of specific dates, they can be given arguments: if you write startOfDay (-1), then the start of the previous day will return. If you leave everything as it is, the start of the current day will be displayed – the output will be the time. These functions help to avoid hardcode, we use them very often.
FROM issueHistory () I already gave an example, this function returns a list of only your views.
linkedIssues () – a function that allows you to find tickets that are linked to a specific ticket.
These are the simplest functions. But let’s dive a little deeper and look at more complex connections.
assignee was currentUser()
AND fixVersion was in
unreleasedVersions()
AND created > startOfYear()
A bit synthetic example, but nonetheless. This is a single request divided into three blocks. After completing the first part of the request, we will receive tickets on which I have ever been a performer or am one at the moment. It is very important that WAS it not only was, but is.
In the second part, filtering is added: we will filter out the received scope of my tickets that have ever been in unreleased versions at the moment. That is, if there was a ticket in this unreleased version and it still has not been released at the moment, but then I transferred the ticket to another version, and it has already been released, then the ticket will be included in this selection.
The third condition is the creation date. We filter only those tickets that have been created since the beginning of the current year.
ScriptRunner Functions
This is a plugin that greatly enhances the capabilities of Jira. It is usually used to automate processes, but it also adds a lot of additional functionality to JQL. ScriptRunner was our very first plugin that we delivered as soon as we moved to Jira – at the end of 2018. I very actively asked to install this plugin, because without it I could not collect data on links with epics. For example, I often needed to return all epic tickets for a specific request or all epics for tickets from subqueries. ScriptRunner lets you do all of this successfully.
To use ScriptRunner functions, you need to add an extra word in JQL issueFunction in or not in… Next comes the function, for example epicsOf () – it returns ticket epics that match the conditions of the subquery. The subquery comes in parentheses on the second line, and we’ll take a closer look at it.
issueFunction in epicsOf
("worklogDate >= startOfWeek(-1) AND worklogDate <= endOfWeek(-1)")
AND project in ("Видео.B2G")In the first example, we are looking for epics with time write-offs for the last week. Life hack for team leads and managers: if you forgot to fill in the time sheets, and don’t remember what you did last week, by completing this request, you will see what epics the team worked on. And most likely, you also worked on them, because the team clearly came with questions. In general, this query helps to remember what you were doing, and everything is fine to paint.
The query itself starts from parentheses, that is, from a subquery worklogDate – date of write-off. Further there is a clarification > = startOfWeek (-1) – beginning of the week. But pay attention to the number -1: it means that we do not need this Monday, but the last one. And also worklogDate <= endOfWeek (-1), that is, it is less than the end of last week. This request will issue tickets, no matter what – bugs, tasks, user stories – for which employees wrote off the time from Monday to Sunday last week.
The trick is that the functions startOfWeek () and endOfWeek () allow you to get rid of the date. Regardless of the period of the current week I make this request, it will give me the same epic scope. As soon as this week ends, he will return the epics for it. Surprisingly, not everyone uses this opportunity: I recently studied open requests that are publicly shared, and saw a lot of hardcode dates there. And there is a suspicion that these dates are constantly changing. And what can I say, in the beginning I did it myself.
By executing the subquery, we get the usual set of tickets. Next comes the function epicsOfwhich gives us a list of the epics associated with these tickets. And then there is filtering by the project, because I only need epics for my project, and everyone else is not interesting.
The next request is for epics with write-offs this year, but no contracts. This request appeared due to the fact that we use Jira not only as a task tracker, but also for financial accounting. There is a separate project for contracts, which we run in the form of tickets, and we use it as an electronic document management system: statuses are constantly changing, we link contracts with epics, we know how many people have written off to which epic, we know how much it costs, and then by we set the cost of work for each contract. Plus, through contracts, labor costs are transferred to Redmine 2.0. That is, we write off to Jira, and then automatic scripts transfer our costs to Redmine 2.0 under these contracts.
When this automation started working, I started receiving requests from colleagues of the kind: there are epics whose labor costs cannot be transferred to Redmine, because there are no contracts there. Let’s consider the request in more detail.
issueFunction in epicsOf("worklogDate >= startOfYear()")
AND issueFunction not in hasLinkType(Contract)
AND project in ("Видео.B2G")The enclosed request means that we are interested in tickets that have been charged for this year. Function epicsOf follows from the previous example and gives us a list of epics. Next, we want to filter by the presence of contracts.
The parenthesized contract is a type of internal link that connects contracts to epics. hasLinkType () – a function in ScriptRunner that returns tickets with this type of relationship. But I need tickets that do not contain this type of relationship, and therefore I use the negation not in.
When the first condition was fulfilled, I got a scope of epics that were relevant this year. Further, epics without contracts were filtered, and in the final – for a specific project “Video.B2G”. This way I got all the epics to work with.
And at the end, I want to suggest passing a small test of three questions on the topic of this post. Will take 2 minutes. After passing you will see your assessment. Poll link: https://docs.google.com/forms/d/e/1FAIpQLSdGrUZZVB62W_1-nC42Aoaz0nO5jUFTK-qIzPDKLX58u5SzCg/viewform?usp=sf_link
I would be glad to clarify something or answer questions in the comments, if you have any.
Thank you.
