It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Becoming a JIRA Search Ninja - The secret powers of JQL

1488465441776-8820e73c-c3fb-4242-a52a-5ff125b525fa_ (2).jpg

JIRA is a cool tool but it becomes even cooler if you know how to leverage it's powerful search features! Some of you probably already discovered the powerful JIRA Query Language (JQL) and that's what this series of blogposts is all about. In the next few posts we'll cover:

The secret powers of JQL

In Advanced searches in JIRA we covered the basics of JQL but there's much much more! We won't be able to cover (allthethings)  but I've done my best selecting the most relevant JQL features.

Filter on Users

currentUser()

What if you would like to find all issues to which you've been assigned. Basically all the work that you are expected to do.

You can use the JQL function currentUser() for this:

 

assignee = currentUser()

And the good news is, this filter will not only work for you but it'll work for others too. This filter is somehow personalized to the logged in user. In the first post of this series we've covered how to save a filter that can be used by others, so if you'd save this filter and make it available to other they'll be able to use the filter to see all issues that have been assigned to them.

 

membersOf()

You could do the same for all members of a certain squad/team/group. The most important thing to note here is the use of IN instead of =

assignee IN membersOf("Collaboration Squad")

Date filtering

In the article about History Searches we covered many ways of filtering by using dates. Most of the examples were using static dates. But Atlassian has provided a set of functions to support dynamic date filtering as well.

startOf...()

There's a couple of functions 'startOf...'. Here's a list:

  • startOfDay()
  • startOfWeek()
  • startOfMonth()
  • startOfYear()

The following example gives you all issues that were created this year:

 

created >= startOfYear()

 

endOf...()

The same functions exist for the end of a day, week, month or year:

  • endOfDay()
  • endOfWeek()
  • endOfMonth()
  • endOfYear()

now()

The now() function refers to the current date and time and is often used for filtering on the due date:

due <= now()

lastLogin()

If you want to keep track of all the issues that have been created or updated since you last logged in, you could use this function:

created >= lastLogin() OR updated >= lastLogin()


Many more

These are just a hand full of available functions. The complete list can be found on the official JIRA Documentation.

Questions/Problems?

Please checkout this page from Atlassian first: https://confluence.atlassian.com/jiracoreserver073/basic-searching-861257184.html?_ga=2.12645040.62589169.1494573379-798135519.1489390862#Basicsearching-troubleshootingTroubleshooting

If that doesn't help, please use the comments.

 

10 comments

Super clear. Thanks!

And how do you search for tasks on Kanban boards or scrum backlog?

Hi Przemyslaw, 

A kanban/scrum board itself is backed by an existing JQL Filter. If you know the filter's name or id you can do the following and add extra clauses to get a more specific result:

filter = 123456 AND ...

or 

filter = 'Filter Name' AND ...

Best,

Maarten

Maarten - I would like to find where assignee is a "memberof" a Portfolio Shared Team, is this possible?  How can it be acheived?

Using the syntax in this article generates the error "..group does not exist.".  Because it is not a JIRA group, but a Portfolio Shared Team.  So was hoping there is another way to utilize the Team from Portfolio without have to create & maintain JIRA groups.

Thanks,

Karen.

Hello Maarten.

Can the startofweek/endofweek/month functions cope with the dynamic dates? I have tried various ways to get startOfWeek to accept parameters like 'startOfWeek("+1w -1d")' but I always get an error:

Duration for function 'endOfWeek' should have the format (+/-)n(yMwdm), e.g -1M for 1 month earlier.

I want to find a way to query for all the Mondays in the next quarter (for example), and I was hoping I could do:

startOfWeek("1w 1d") OR startOfWeek("2w 1d") OR startOfWeek("3w 1d")...

etc

 

I'm trying to figure out how I can set up a filter where the watcher is the current user, but the assignee is not the current user.  I don't know SQL and have had a go at trying to make this work but keep hitting blocks.  Any suggestions please? 

Ooh! I figured it out (and how to use the search functions on this site!)

@Olivia Ross - Sorry for the late response. You should be able to use dynamic dates in the startOfWeek and endOfWeek functions.

You can only pass in one item (so just -1w or -2w not -2w -1d). So in your case startOfWeek("-8d") OR startOfWeek("-15d") OR startOfWeek("-22d") should be accepted. But if you want all issues created on the past 3 mondays:

(created <=  startOfWeek("-5d") AND created >= startOfWeek("-6d")) OR (created <=  startOfWeek("-12d") AND created >= startOfWeek("-13d")) OR (created <=  startOfWeek("-19d") AND created >= startOfWeek("-20d")) 

This query takes into account the fact that created contains time as well. (start of day and end of day).

Hope this helps.

Best,

Maarten

I'm trying to create the following search Parent Link in (filter=30760).

In other words, return the issues where the Parent Link is in the result of a predefined filter.

where Filter=30760 is: Issuetype = Initiative.

 Ideally I'd like the children, and grandchildren. This is to help me set up a portfolio plan.

Help?

Comment

Log in or Sign up to comment
Community showcase
Posted in Jira

Calling all Jira Cloud users! Give us feedback on our exploration of a new navigation.

Hi everyone! My name’s Matt and I’m a product manager at Atlassian. I work in the navigation & findability space for all our Jira Cloud products. We’ve been working on trying to improve the exp...

967 views 15 12
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you