The last time I spoke about JQL was in June of 2020. The only other time I spoke on it was in April – only a few months before that. For one of the critical features on Jira, it’s been too long since we spoke on it. So that is what we are going to fix today. I’ve gone through and Identified six bits of JQL that will help you take your querying to the next level.
As always, I will be talking about functionality that is in Jira “Out of the box.” Of course, you can always add functionality to Jira through the Atlassian marketplace and many Apps to add querying functionality specific to their use case. However, it’s hardly fair for me to show you some great functionality only to snatch it away behind a paywall.
So let’s dig into this and see what functionality we can uncover!
watchedIssues() and votedIssues()
In “JQL – How have we not talked about this before?” I showed you how to search the watcher field to find all the issues you are watching. This functionality is handy if you are trying to cut down on Jira Spam, so you want to bulk unwatch issues.
issue in votedIssues() issue in watchedIssues()
However, I found two functions today that make this even easier. So, I want to introduce to you the watchedIssues() and votedIssues() functions. These return the issues you are either watching or have voted on, respectively. Simple as that. watcher=currentuser() becomes issue in watchedIssues().
This also means you can use these functions in new ways to find issues you are specifically interested in. For example, maybe you are trying to find an issue that you edited a few weeks ago but otherwise are not assigned to or involved in. Because you edited it, you will have been added as a watcher, which means you can use this function to go back and find it.
Fun fact, did you know that you can query the issues you have recently looked at? No really. The issueHistory() function returns all issues that appear in the Issues -> Recent Issues section of Jira.
issue in issueHistory()
To be completely honest, I’m not sure where you would use this. I mean, you could query it, or you can go to Issues -> Recent Issues. My only thought is if you would want to include this list in a shared dashboard. Don’t get me wrong, I’m all for saving clicks, but this does seem to take that to an extreme. Oh well, still a cool trick!
projectsWhereUserHasPermission() & projectsWhereUserHasRole()
Let’s say you are trying to find which projects you are an Admin on in Jira. How would you do that? Previously, the answer would be “Audit all the projects, and take notes for any you find?” What if I said I had found a better way?
Now you can use JQL to find all projects where you have a specific Permission or Role. You can now narrow down the issues returned to projects where you either have a specific permission node or a specific role. Be aware, though, these functions are not syntax-help friendly, so you have to make sure you have the spelling is correct yourself. However:
project in projectsWhereUserHasRole("Administrators") and project in projectsWhereUserHasPermission("Delete Issues")
Using this, you can eventually narrow down to a complete list of Projects where the Permissions and Roles are true. Combine this with an App like Su for Jira, and you can save so much time auditing a specific user’s access. Personally, this is the best find of everything I have in this article!
componentsLeadByUser() & projectsLeadByUser()
These next two are also incredibly handy for Auditing a user’s access, especially as you can use these to query other users. Let’s take a look at how they are used:
component in componentsLeadByUser(username) project in projectsLeadByUser(username)
The first one will return all issues that have components that are led by the username specified. If you specify no username, it will return all issues that have Components led by you. Likewise, the second one returns issues in Projects led by the user you put in there. Again, you can leave it blank to include all issues in projects led by you.
On top of Auditing, these are useful for leads as it lets them quickly narrow down issues to the subset they need to be mindful of – without having to specify projects or components by name. In addition, these can be used to build robust dashboards that don’t have to be updated every time a new project is created or a new component is made.
So, question. How do you query all issues in an Epic? This is one case where how you do it will vary on what deployment model you use.
If you are on Cloud, congratulations, you have it in easy mode. You can use the parentEpic function to return all issues belonging to that Epic. For example, to return an epic and all issues belonging to it, type:
issuekey in (EPC-123) or parentEpic not in (EPC-123)
If you are on Server or Data Center, you can use the Epic Link to achieve a similar result. A caveat, though: this will only return the issues directly attached to the Epic – it will not return those issue’s subtasks (should they exist). So, for example, to return an Epic and its direct children, you can type the following:
issuekey in (EPC-123) or "Epic Link" in (EPC-123)
Again, you can find the further subtasks using Scriptrunner to expand your JQL. However, I want to stick with what an otherwise unmodified Jira can do, so I won’t cover those today.
Searching for Issue Links
So, you have decided you want to search for Issues with Links. Once upon a time, the only option to do this was to use an App to extend your instance’s JQL functionality. However, that is not the case today. And this blew my mind when I found it while researching this post. I mean – this changes a lot on how we can find these issues. For example, specifying blockers becomes much more helpful if I can then list out all the blockers in a project.
Now, there are two functions I want to share here. Our first one is well documented. The second one is not so much – but I’ve tested it on an otherwise unmodified Jira instance, and it works. So let’s start with linkedIssues(). This is a function that lets you find all issues linked to the one you specify. It is used like this:
issue in linkedIssues("TEST-4")
If you are so inclined, you can even specify the type of link as an optional second parameter. This setup can be handy when you use many link types, but you are only interested in blockers today.
issue in linkedIssues("TEST-4", "is blocked by")
However, because you are limited to searching for links one issue at a time, this is only so helpful. This situation is why I’m so excited to share with you the issueLinkType function. If you give this one or more Link Types, it will return all issues with that Link Type.
issueLinkType in (blocks, "is blocked by")
This is an enormous query here. Now you can return all issues that have specific Link Types associated with them. For example, issueLinkType = “blocks” returns all issues that are blocking something. Pair this with other terms to narrow down the scope, and you suddenly have a handy list of first-priority action items.
However, this function is a bit odd. I looked up all of these JQL terms in Atlassian’s official docs for both Cloud and Data Center. This one is not on the list. I thought this strange, so I put my Test Instance into safe mode – which disables all third-party Apps. I was running pure Jira, and this still worked. I also asked some friends about it, and they confirmed it worked for them as well on completely different Jira instances. So we have something that is a part of Jira, but not stated as such.
I can only guess as to why this is the case. It could be a simple oversight – public Documentation is usually the last task before release, and it tends to be rushed a bit. Or, this could be a computationally intensive search, and Atlassian doesn’t want to advertise it to avoid making Jira seem slow. I’m honestly not sure. But I know this is useful and works, so I’m happy to have it in my toolbox.
What are your favorite JQL Tricks?
Please share them with me, and if I get enough, I’ll put together another JQL post featuring your suggestions!
Don’t forget you can find links to all my social media on Linktree. Please be sure to follow me, like the post, and add a comment. Especially add a comment – commenting on my post is the best way to help more people learn about the blog.
You can also subscribe to the blog directly below. This allows you to be one of the first to read new posts, as they will be delivered directly to you in an email!
But until next time, my name is Rodney, asking, “Have you updated your Jira issues today?”
another interesting fact about the “issueLinkType” function is, that subtask relationships are (internally) also stored as issue links. The links are hidden in the frontend but they can still be searched via JQL with the types “jira_subtask_outward” (tasks) and “jira_subtask_inward” (subtasks).
See here: https://developer.atlassian.com/server/jira/platform/database-issue-fields/#subtasks
This allows two rather specific but nonetheless sometimes useful queries that are otherwise only possible with third-party Apps:
– Find tasks that have no subtasks: issueType = Task AND issueLinkType NOT IN (jira_subtask_outward)
– Find tasks that have at least one subtask: issueLinkType IN (jira_subtask_outward)
LikeLiked by 1 person
That is so cool! I had no clue, thank you for sharing!
LikeLiked by 1 person