JQL Hell

Jira is intricate: untangling your search criteria can feel like deciphering ancient scripts. The catch-22 of Jira documentation is real – you can't hunt for a solution to your predicament unless you're already versed in the very JQL lingo that's causing you headaches.

So, here's a rundown of the "I wish I knew that..." moments I've stumbled upon, and the ways I've wrangled them back into submission, all spelled out in plain English.

This post is inspired by https://dangitgit.com/en

I wish that I knew how to find tickets assigned to me that are due this week.

assignee = currentUser() AND dueDate >= startOfWeek() AND dueDate <= endOfWeek()

This query lets you quickly see which of your tasks need to be completed within the current week, helping you prioritize effectively.

Want to see older tasks? Get rid of dueDate >= startOfWeek()

I wish that I knew how to identify high-priority tickets that haven't been updated recently.

priority = Highest AND status != Done AND updated <= -14d

Spot high-priority tasks that may have fallen through the cracks by finding those not updated in the past two weeks.

I wish that I knew how to locate all the tickets related to a specific epic.

parent = TICKET-KEY

What used to be EPIC-KEY has now been recently changed to parent! Exciting stuff.

I wish that I knew how to filter tickets that have changed from Done to In Progress recently.

status changed from Done to "In Progress" AFTER -7d

Catch tasks that were marked as completed but have recently been reopened, indicating possible issues or further work needed.

I wish that I knew how to filter tickets that were moved recently by a certain person

status changed BY TYPE_USER_HERE AFTER -7d

Curious about what tickets have been moved by a particular person recently? Use this query.

I wish that I knew how to query for all epics that have been in progress for over 2 months

type = Epic AND status = "In Progress" AND status CHANGED TO "In Progress" BEFORE -60d

This query can assist in highlighting ongoing work that might need reevaluation or additional support. Generally, epics are meant to be complete in less time.

I wish that I knew how to find all the tickets created by me but assigned to others.

reporter = currentUser() AND assignee != currentUser()

Track the progress on issues you've reported but are being handled by someone else, ensuring nothing is overlooked.

I wish that I knew how to find all unassigned tickets in my project.

project = myProject AND assignee is EMPTY

Identify tasks that need to be assigned, ensuring every task has someone working on it.

I wish that I knew how to see all the tickets in my project with attachments.

project = myProject AND attachments is not EMPTY

Quickly find tasks with attached files or documents, useful for reviewing added materials or evidence.

I wish that I knew how to filter tickets updated by me last month.

updated > startOfMonth(-1) AND updated < endOfMonth(-1) AND updater = currentUser()

Review the tasks you've worked on or updated in the previous month, great for reflecting on your work.

I wish that I knew how to identify tickets that are blocked.

issueLinkType = "is blocked by"

Discover tasks that are holding up others, helping prioritize issues to unblock your team's progress.

I wish that I knew how to find tickets that mention a specific keyword in the comments.

comment ~ "urgent"

Locate tasks flagged as urgent or containing specific keywords in comments, ensuring critical issues are addressed promptly. If you want to search all free text fields including things like comments and descriptions, use text instead of comment.

I wish that I knew how to discover tickets with comments.

comment ~ "anything*" OR comment !~ "anything*"

Find tasks that may need discussion or clarification, indicated by the existence of comments.

Note: out of the box, JQL for comments is quite limited. I couldn’t figure out a way to query for tickets WITHOUT comments. That being said, if you go to the bottom of this article, you’ll find extensions that make your life better for searching comments including the exposure of a commentCount field.

I wish that I knew how to list all tickets closed this year.

status changed to Done AFTER startOfYear()

Get an overview of all tasks completed since the beginning of the year, useful for assessing productivity and accomplishments.

I wish that I knew how to find tickets with more than 5 sub-tasks. (plugin needed)

numberOfSubtasks > 5

Requires ScriptRunner plugin. Scroll to the bottom for a link. Manage complex tasks by identifying those with a large number of sub-tasks.

I wish that I knew how to check for any tickets updated over the weekend.

updatedDate < startOfWeek(1d) and updatedDate >= startOfWeek(-1d)

Unfortunately, JQL doesn’t have a concept of a day of the week so you can only query for the most recent weekend.

Let’s say you wanted to extend this to tickets from the past four weeks, you’d need to copy-paste it four times similar to the following:

(updatedDate < startOfWeek(1d) and updatedDate >= startOfWeek(-1d)) or (updatedDate < startOfWeek(-6d) and updatedDate >= startOfWeek(-8d)) or (updatedDate < startOfWeek(-13d) and updatedDate >= startOfWeek(-15d)) or (updatedDate < startOfWeek(-20d) and updatedDate >= startOfWeek(-22d))

I wish that I knew how to see all the tickets in my project with a specific label.

project = myProject AND labels in (myLabel)

Filter tasks by specific labels, making it easy to categorize and find related tasks within your project.

That’s a wrap! But there’s more…

Want to get fancier?

• This extension makes JQL even more powerful: https://marketplace.atlassian.com/apps/1214791/jql-search-extensions-for-jira
• This one does too: https://marketplace.atlassian.com/apps/6820/scriptrunner-for-jira
• Also, don't forget about chatgpt for more JQL examples!

Prefer Substack? https://open.substack.com/pub/strategicspokes/p/jql-hell