JQL Tips for Product Managers
While many product managers know how to use Jira (a ticket tracking tool), they may not have mastery over some of the finer nuances of Jira, including JQL.
What is JQL? JQL is “Jira query language” - think of it like SQL but for Jira. It’s a flexible and powerful way to look for a set of Jira tickets.
Why would you need to query your Jira tickets? Well, here's a sample of the types of things that I’ve had to do as a product manager:
- Identify all of the previous bugs
- Look for sprints that had a lot more bugs than usual
- Create a report on the various kinds of tasks that your team tackled last quarter
- Determine whether a teammate has been overstaffed across multiple sprints in the past
- Find tickets that my executive team specifically cares about
If I didn’t have the ability to query my Jira tickets, I would have had a much harder time compiling the information that I needed to!
Here’s the interface for how you can access JQL, which is far more powerful than the vanilla filters that Jira has pre-configured for you. In the “browse” view for your tickets, click on “Advanced” and you’ll see the search query come up. You can find an example at https://jira.atlassian.com/browse/JSWCLOUD-17214?filter=98153
Here’s the thing - I already know that there’s plenty of documentation out there about JQL. But what I’ve noticed is a lack of prescriptive guidance on which queries are the most useful, and how to build on top of your previous knowledge to expand your skillset.
So, here are my favorite 14 Jira queries, so that you can quickly summon up the tickets that you need. For each query, I’ll walk through what it does, how to enhance it, and how to weave it into your day-to-day product management processes.
- assignee = clement
- assignee was clement
- assignee is empty
- reporter = clement
- watcher = clement
- text ~ “form submission”
- project = MYPROJ
- issueType = bug
- order by priority desc
- order by created desc
- order by resolutiondate desc
- status in (“open”, “in progress”)
- status not in (“blocked”, “closed”)
- sprint is empty
Let’s dive into each one below.
1) Assignee = Clement
Say that I’m looking for the tickets that I’ve been assigned to. I would write the JQL query “assignee = clement” to look for those.
How does this work?
The first word “assignee” is a field, and this field is within every Jira ticket that you have.
The second word “=” is an operator, and we use operators to tell Jira what kind of equation we’re trying to build.
The third word “clement” is a value, and we use values to tell Jira what we’re looking for.
So, “assignee = clement” is telling Jira to first look at the “assignee” field of all of the tickets, and then check to see whether any of those assignees happen to be “clement” (which is my Jira username).
Of course, it’s not sufficient for you to know how to look for your own tickets. You might want to look at the tickets that are assigned to other people too. How would you do that?
Let’s say that I wanted to look for the tickets that are assigned to my co-founder Kevin. Then, I’d change the JQL query to say “assignee = kevin” - it’s thankfully pretty straightforward.
I can use the assignee query to identify whether one of my teammates is overloaded with work. If so, I can quickly reassign to someone else, and ensure that we’re distributing the load evenly.
But, there’s a twist. The problem with the “=” sign is that it only checks for the current state of each ticket. What if I need to look back into the past?
As an example, for some of the Jira boards that I’m a part of, the assignee first starts with the product manager, then the engineer, then the quality analyst. Then, once the ticket has passed the test cases and moved into production, the ticket automatically becomes unassigned.
If I wanted to know the tickets that Allie had already completed, I can’t get those tickets with “assignee = Allie” - after all, all of our completed tickets are unassigned.
So, let’s use “was” instead!
2) Assignee Was Clement
I’m a huge fan of using “was” since I’m no longer constrained to the present. If I use the query “assignee was clement”, I can see every single ticket that had been assigned to me - even if I’m no longer assigned to the ticket.
The great thing about the “was” operator is that it also includes the present. So, if I’m the current assignee of ticket MYPROJ-123, the query “assignee was clement” will still give me MYPROJ-123 in its results.
So, we can now travel back in time. But what about dealing with tickets that have slipped through the cracks, and aren’t assigned to anyone at all?
3) Assignee is Empty
In this query, we’re learning two new concepts at once. First, we’re tackling the operator “is”; it’s pretty similar to its SQL equivalent.
In SQL, if you’re looking for the state of a field, you’re not using the syntax “assignee = NULL”. Rather, you use the syntax “assignee IS NULL”, since NULL is not a value.
The thing to keep in mind, though, is that JQL doesn’t recognize NULL. It recognizes “empty” instead.
So, now I can look for tickets where no one’s been assigned. This can be really helpful in cases where you’re trying to track down tickets that have stalled out, or tickets that accidentally lost their assignees.
4) Reporter = Clement
Okay, but this is still a bit limiting. I haven’t been able to look for anything outside of “assignee” yet. What if I need to mix it up? For example, let’s say my boss reported a bug, and I have no clue which bug it was. How will I get that information?
Thankfully, Jira has a field called “reporter”, which tracks who created the ticket in the first place.
So, if I’m looking for tickets that my boss Alden created, I can write the JQL query “reporter = alden” to look for the bugs he’s reported.
But, let’s say that that’s not enough. What if I need to find the tickets that my boss has reported, and also look for the ones that have no assignees yet?
I can add the “AND” keyword to the JQL, and now I can look across two things at once.
If I write the JQL “reporter = alden and assignee is empty”, I now get all of the tickets that my boss reported, but only if there’s no one assigned to the ticket.
So, I can use this to quickly check whether the tickets that my boss is worried about have been assigned to someone yet. That’s handy.
5) Watcher = Clement
But maybe my boss doesn’t regularly file Jira tickets. Perhaps he subscribes to tickets that he’s particularly interested in, such as tickets that impact large enterprise customers.
In Jira, you have the ability to watch tickets. When you watch a ticket, you get notifications in your email; you’ll be alerted to status changes, comments, and even changes in the description of the ticket.
But, there’s a caveat - reporters aren’t necessarily watchers, and watchers aren’t necessarily reporters.
So, now I’m going to use the “OR” keyword to check across both fields. If I use the JQL query “reporter = alden or watcher = alden”, I can now track all of the tickets that my boss has filed and all of the tickets that my boss is watching.
But let’s say that I’m still interested in ensuring that I only get the tickets that have no assignees. After all, those are the tickets that require my intervention.
We’ll now add in “()” to group logic together.
So, I’m now going to write “(reporter = alden or watcher = alden) and assignee is empty” as my query.
Why do I do that? Well, if I put the “OR” in the wrong place, it’ll give me the wrong answer. For example, if I wrote “reporter = alden and assignee is empty or watcher = alden”, then I’ll get all of the tickets that my boss is watching, regardless of assignment.
6) Text ~ “Form Submission”
This is great and all, but what if I’m looking for a specific ticket? For example, let’s say that I got some new bug report where one of my webpages crashes due to a bug in the form submission process. I’m trying to find out past tickets that have tackled a similar problem like this, so that I can narrow down the root cause of the issue.
No problem! You can use the query “text ~ “form submission”” to look for any ticket related to form submission. Jira will look across both the title of the ticket as well as the description of the ticket.
Note that if you want to limit yourself to search only in the comments, then you can use “comment ~ “form submission””, and if you want to limit yourself to just the description, then you can use “description ~ “form submission””. Also, if you want to search for the name of the ticket, use “summary ~ “form submission””, since Jira calls it a summary.
Okay, but what is “~”?
Think of it meaning “like”, rather than “strictly equals”. In other words, Jira will also look for words like “submit” and “submitted”, rather than only looking for the word “submission.”
7) Project = MYPROJ
Let’s use the keyword “project” to look for particular projects that I’m interested in!
Perhaps I have two projects, MYPROJ and NEWPROJ. It’s helpful to use the “project” keyword as a filter so that I don’t get confused about which tickets I’m looking at.
So, I can look for “project = MYPROJ and assignee is empty” to quickly look for the tickets in MYPROJ where no one’s been assigned.
8) IssueType = Bug
Products will invariably have bugs. What if I want to know about all of the bugs that are part of my particular product?
I can use the “issueType” keyword to look for bugs. So, if I say “project = MYPROJ and issueType = bug”, I can look for all of the bugs associated to MYPROJ.
I can also use different kinds of issueTypes, such as story, task, and epic. For example, if I want to know about all of the epics that are within NEWPROJ, I can type “project = NEWPROJ and issueType = epic” to find my answer.
Okay, back to bugs. I really want to know about the worst bugs first, so that I can deal with any ongoing fire drills. How do I sort the results that I’m getting back?
9) Order by Priority Desc
We’ll now use “order by” to tell Jira that we want our results sorted in a particular way. Here, I’m sorting by the “priority” field in Jira. At work, our tickets have 5 different priorities:
- P0 = blocker
- P1 = critical
- P2 = major
- P3 = minor
- P4 = trivial
But what’s “desc”? That tells Jira how I want my tickets to be ordered. You only have two options here: “desc” and “asc”. That’s actually quite nice, because if I wind up picking the wrong order, I can just change the order with a couple of keystrokes.
So, now I’m going to look for “project = NEWPROJ and issueType = bug order by priority desc”, and that’ll give me all of the bugs in NEWPROJ, sorted by highest priority first.
10) Order by Created Desc
Now, let’s say that I’m looking for bugs that have been filed a while ago. If we haven’t resolved those yet, that could be problematic.
No problem! I can order by “created” to look for tickets that have been around awhile. And, I can combine this together with my other “order by priority” so that I’m first looking at the highest priority bugs, then looking at how long it’s been since it was created.
Here’s the query now: “project = NEWPROJ and issueType = bug order by priority desc, created asc”
Notice how I used a comma between my two “order by” clauses. It will first order all of the tickets by priority, so all of the P0 tickets are together, all of the P1 tickets then come after, etc.
After that, then it orders by the date it was created, but only within the first sort. So, I’ll see my oldest P0, then a newer P0, then a newer P0, etc., only until I run out of P0 tickets. Afterwards, I’ll then see the oldest P1, then a newer P1, then a newer P1, etc., until I run out of P1 tickets.
11) Order by Resolutiondate Desc
Okay, so I have the date that the bug was created. But how do I know whether it was fixed or not?
One way to get around that is to use the “order by resolutiondate”, so that I can see the tickets that were recently resolved. For example, now I can say “project = NEWPROJ and issueType = bug order by priority desc, resolutiondate desc” to look for the highest priority bugs that had the most recent resolution dates.
12) Status in (“Open”, “in Progress”)
What if I’m trying to see whether a bug is being tackled or not? Well, I can use “status” to look for it! In Jira, you’ll have different ticket statuses - check to see what statuses you currently use in your project.
For my project NEWPROJ, there are only 4 statuses: open, in progress, blocked, and closed.
So, now I can look for bugs that are either open or in progress. I can do that by using “in” to pick out multiple options at once, rather than having to write a bunch of “OR” statements.
The query now looks like this: “project = NEWPROJ and issueType = bug and status in (“open”,”in progress”)”
This gives me all of the bugs in the NEWPROJ project and restricts my results only to those that are open or in progress.
13) Status not in (“Blocked”, “Closed”)
But, what if I have too many statuses? For example, what if I had fifteen different statuses that all meant some sort of “in progress”, and I only want to look for tickets that are not blocked or closed?
I can do that by using “NOT”, like this: “project = NEWPROJ and issueType = bug and status not in (“blocked, ”closed”)”.
In a project where I have only the four statuses that I mentioned, this is functionally the same as the one where I said “project = NEWPROJ and issueType = bug and status in (“open”,”in progress”)”. But in projects where I have a lot of statuses, I can use “not in” to make my life easier.
14) Sprint is Empty
Okay, last one. I need to find the bugs that are in NEWPROJ that aren’t yet allocated to a sprint, so that I can decide which ones to move into the sprint.
JQL uses the concept of “empty” to say “there isn’t such a thing.” For example, “sprint is empty” means a ticket doesn’t belong to any sprint.
So my final query is “project = NEWPROJ and issueType = bug and status in (“open”,”in progress”) and sprint is empty” to look for all of the tickets that I might need to move into a sprint.
By investing time into fully understanding the tools that you use on a day-to-day basis, you’ll not only be more productive, but you’ll also unlock new capabilities that you didn’t know you had.
By taking the time to learn about JQL, you’ll be able to stay on top of your tickets much more easily.
Have thoughts that you'd like to contribute around product manager coffee chats? Chat with other product managers around the world in our PMHQ Community!