My manager has historically done a lot of work in Jira, evaluating and triaging the many tickets our team receives. As his responsibilities grow, some of the senior members of the team have started to take on that triage work. We’re learning a lot about the different types of requests we get and how we prioritize those requests, which sometimes mean we have to reconfigure our queue to account for a new ticket that has to jump the line.
This week, my manager told me that he had selected a group of tickets to de-prioritize based on a JQL query. It had never occurred to me that JQL existed so, considering the circumstances, I decided to write a blog about it.
Jira Query Language
I used my prior knowledge and context clues to figure out that “JQL” was Jira Query Language. After all, we use SQL, which stands for Structured Query Language and we’re once again querying data, just this time we’re doing it in Jira.
If you’re not familiar with Jira, it’s an issue and project tracking software that’s practically ubiquitous in the world of engineering. It’s efficient to break up large projects into smaller tasks, give those tasks due dates and descriptions, and comment on the tasks as we make progress. Doesn’t this look nice?
Since I’m a Solutions Engineer, we more frequently work on shorter-term tickets than large projects, but we use Jira nonetheless. And being able to filter tickets is a big help when we’re triaging or if we want to look up something we’ve done before. Naturally, Jira has a search UI for this. But if we want an advanced search, we can actually type JQL into the search bar.
If we don’t properly format our JQL, it’ll be interpreted as a search for text contents. When I wrote this in the search bar:
date >= 10/15/2021I got one result even though the 15th was a Friday so plenty of tickets were created on that day. I was using the search field in the top-bar on every Jira page, but my failed search brought me to a Search page, which provided an auto-populated drop-down and auto-fill options
So I could already see that my query wouldn’t work because date isn’t a property of Jira tasks in this environment. Clicking “Syntax Help” brought me to Jira’s Advanced Search documentation, which had a section for available fields that I could query. I clicked there and searched for “date.”
It turns out that there is no field for “date” because that’s too vague. Tickets can have a created date, a due date, a date last viewed, and more, but we want tickets that were created on a certain date, so I altered my query:
createdDate >= "2021/10/15"I changed the format for the date and made it a string because that’s how I would have written it in SQL. Luckily for me, JQL works the same way. One more cool thing about the Search page is that the auto-fill is really smart:
It recommended a bunch of date functions that I could use instead of putting in a date string. Cool!
Other JQL Options
What else can we do with JQL? Well if I wanted to add parameters to our query, we can add and:
createdDate >= startOfMonth() and assignee = Mike DiazIncidentally, if I type my name, the Search page will automatically populate my user ID. Beyond that, the most useful thing I could learn would be how to query text within fields. Our tickets often have similar titles, so if I want to find examples where we had an SFTP request:
createdDate >= startOfMonth() and summary ~ "SFTP"The biggest challenge for me here was figuring out what Jira calls the a ticket title (“summary”) and what the call the body (“description”). Once I figured that out, I could use the CONTAINS operator (“~”) to search within that field. The values we’re searching for have to be wrapped in quotes, but if we add a space, they’ll be interpreted as two different values to search for. This query would return results where “SFTP” or “files” were found in the description:
description ~ "SFTP files"If I wanted to search for that single phrase, I’d have to add another set of quotation marks. But since they already have a default function, we must use backslashes to escape them and indicate that they’re serving a different purpose than the other quotation marks:
description ~ "\"SFTP files\""We also have the option to use a wildcard if we want to partially match a word. The example Jira provides is:
win*Which matches Windows, Win95, or WindowsNT.
Syntax Learning Curve
It’s always intimidating to try and learn something new. As programmers, we tend to see others pick up new languages or frameworks without much trouble at all (maybe it’s easy for them, maybe it’s not…but I’ve definitely seen people make it look easy). In any case, the learning curve does flatten because of an underlying common logic. It was challenging for me to figure out what different fields are called and how to use CONTAINS, and I’m sure there’s still a lot I don’t know about JQL. When I think back to learning SQL, though, I realize that it took me about twice as long to get to where I am with JQL now. That’s progress and that’s what we’re all about!
Sources
- Jira by Atlassian
- Use advanced search with Jira Query Language (JQL), Jira
- JQL: the most flexible way to search Jira, Dan Radigan, Atlassian