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 encountered 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 allow you to 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, you do not need to select specific fields that we will select, indicate tables and databases from which we will display. 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, 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 () and others).
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 system field lastViewed . Jira keeps track of your ticket views.
Here are two options for composing filters for viewing recent tasks. The first is my lastViewed option , where Jira will return the issues I've viewed in the last seven days, sorted in descending order. This filter is configured 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 IssueHistory () function , sorting is also done by the lastViewed field . The result is the same, but the method, even in Jira, can be used differently. It is worth noting that the LastViewed and IssueHistory () fields only return your browsing history - you cannot view the history of third parties this way.
For the most part, Jira has standard operators. My favorite operators are WAS , WAS IN , WAS NOT IN , WAS NOT , CHANGED because they work with time. This is not possible in conventional databases.
Jira allows you to work with historical data out of the box. Using the WAS operator, you can find tickets where the executor 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: “ Bring me tickets in which the Summary field contained the word“ Rostelecom ” ”.
The second example is with the CHANGED operator . We want to receive tickets in which the artist was changed after January 1, 2020. You can use other additional words, for example, 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 on which users are stuck : 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 get a complete set of tickets from two projects A and B. If we need to narrow the selection, we use AND . Example - we need tickets from the draft A, on which the user was executing Bed and: project = A = Bed and the AND assignee . 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 () is a popular function 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. With currentUser (), you can do generic searches. This is how I made a universal dashboard for the entire development team: I stuffed gadgets onto the dashboard and in each I indicated currentUser () instead of a specific user . 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 start of the current day. There are functions for week, month and year. The same applies to the endOfDay () closing function . They allow you to get rid of specific dates, you can set arguments to them: if you write startOfDay (-1) , then the beginning of the previous day will be returned. 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 () is 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 query divided into three blocks. After completing the first part of the request, we will receive tickets on which I have ever been an executor or am currently one. It is very important that WAS not only existed, but still exists.
In the second part, filtering is added: we will filter the received scopes 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
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 additional word issueFunction in or not in in JQL . Next comes the function, for example epicsOf () - It returns ticket epics that match the subquery conditions. 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 executing from parentheses, that is, from the worklogDate subquery - the date of debiting. Further there is a specification > = startOfWeek (-1) - the 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 startOfWeek () and endOfWeek () functions 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 epicsOf function , which 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 in 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. The epicsOf function follows from the previous example and gives us a list of epics. Next, we want to filter by the presence of contracts.
A contract in brackets is a type of internal link that connects contracts to epics. hasLinkType () is a function in ScriptRunner that returns tickets with this link type. 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 linkI would be glad to clarify something or answer questions in the comments, if you have any.
Thank you.