Help wanted: Insane JQL

[Anton Prokhozhy]

Hi all. I'm making a small deck on the basics of Jira for my teams, currently working on explaining JQL. I'm more or less done with the basic slides, but I had an idea for an extra exercise for the most dedicated reader.

I need a couple of crazy, insanely complex jql queries to put at the end of the module, so my people can try and decypher them. if you have any handy this is an invitation to share :)

edit: based on the submissions so far, it looks like the exercise slides can be structured based on difficulty, with an easy, medium and hard option (maybe we will get to insane eventually). As a thank you to the community for this assist, I will be sharing a version of the jql module of the deck in this post around Wed-Thu, once I get a chance to finalise it and edit out any proprietary company content. Feel free to use these materials in your teams if you feel they will help you. And feel free to add more queries :)

[Colum McAndrew]

OK I'm not sure if this is "insane" enough, but here goes...

project = xxxxx AND type = epic AND status != "Done" AND "Target end" <= startofday(60) AND status != Cancelled ORDER BY "target end" DESC

Purpose is to list all epics on our project due within the next 60 days.

Over to others to beat this, which I'm sure won't be difficult. The competition has officially begun. This should be fun :-) 

[Anton Prokhozhy]

thanks. if we don't get anything weirder i'll use your filter as a starting point and add some nested brackets and obscure functions or something :)

[Bill Sheboy]

Hi @Anton Prokhozhy 

One challenge with what you are trying is that your team *could* decipher what a JQL expression is supposed to do, but open defects may prevent that expression from working.  So maybe try some "beware this" queries which combine information from company-managed and team-managed projects, such as status values and similarly named custom fields.

Another thing to try if your team is SQL savvy: provide a SQL statement and ask how to reproduce it in JQL, or answer it is impossible with out-of-the-box features.

You could also probably find some ideas by paging through this documentation: https://support.atlassian.com/jira-software-cloud/docs/use-advanced-search-with-jira-query-language-jql/

Finally, when I have done what you describe in the past with teams, I try to make it practical: create basic and advanced use cases by role and describe the how/why of them.  For example: "Day in the Life of a Product Owner" use cases.

Kind regards,
Bill

[Anton Prokhozhy]

I am going to specifically try and write them in a way that a search won't work in our instance. The goal of the exercise as i see it is to give the reader a roadmap to start using the reference documentations, and the control is sending the expected result via personal message to a member of my team.

Regarding the SQL idea - unfortunately it won't fly, we're scaling Jira out of IT to every team in the company, and most of them are already terrified of JQL as is :)

The documentation is referenced on every page of the JQL module of the deck. But I'll definitely scan through it again as you suggest, thanks.

Regarding the last point - I used to have a whole library of these while I was doing a similar onboarding initiative at my last gig. Unfortunately I just can't seem to find that document anymore.

All in all, thanks for the suggestions!

edit: after re-reading your comment i've added some of the ideas from your first point into the main presentation. thank you

[Rich Wolverton]

Simple and useful. Note we have a separate custom text field for Status Comment

(description ~ currentUser() OR "Status Comment[Paragraph]" ~ currentUser() ) AND updated > startOfDay(-2) ORDER BY updated DESC

[Anton Prokhozhy]

oh, wow, i was unaware that this function was supported for text type fields. thanks so much!

[Josh Costella]

Here's one. Looking for a few more. 

category = product AND issuetype = bug AND "Found in Environment" = prod AND resolved >= startOfMonth() AND resolved < startOfMonth(1) AND (status was not in (Done, "Done / Accepted") on endOfMonth(-1) OR status changed from (Done) after endOfMonth(-1) AND resolution changed to EMPTY) AND resolution = Done ORDER BY created DESC

[Anton Prokhozhy]

Awesome, exactly the kind of thing I’m looking for. Intimidating, but readable. Thank you!

[Marc - Devoteam]

Another one for you.

project = <project-name> AND (type = Story OR type in (Task, Sub-task) AND component = QA AND (labels is EMPTY OR labels not in (Test1, Test 2))) AND status not in (New, Cancelled, Done) AND (labels is EMPTY OR labels != ACC) AND (issueLinkType != <link-type> OR issueLinkType is EMPTY) ORDER BY Rank ASC

[Anton Prokhozhy]

could you expand on this one a little? i feel like i'm not entirely understanding this part:

type = Story OR type in (Task, Sub-task) AND component = XXX

feels like either a set of brackets is missing, or "story" should also be grouped with the rest of them.

option 1:

type = story OR (type IN (task, subtask) AND component = XXX)

 option 2:

type IN (story, task, subtask) AND component = XXX

it could be a third thing which i'm not able to parse yet. please let me know which case we're looking at here. thanks!

[Marc - Devoteam]

Hi Anton,

This clause is brother then you are mentioning, the clause in brackets is; 

(type = Story OR type in (Task, Sub-task) AND component = XXX AND (labels is EMPTY OR labels not in (XXX, XXX)))

We only want to find issues of the type story and the component is XXX or empty and with empty labels or labels not set as XXX or XXX 

Or find issues of the type task and sub-task and the component is XXX with empty labels or labels set as XXX or XXX 

[Anton Prokhozhy]

Ok, I just tried to recreate your description the way I normally write these:

(type = Story and (component = XXX OR component IS EMPTY) AND
(labels IS EMPTY OR labels NOT IN (XXX, XXX))
OR
(type IN (Task, Subtask) and (component = XXX OR component IS EMPTY) AND
(labels IS EMPTY OR labels IN (XXX, XXX))
ORDER BY Rank ASC

As you can see, I group these in brackets - mainly because I start to lose the plot while combining AND and OR operators without these groupings.

I have a feeling my confusion stems from the XXX thing. Would it be possible for you to reformat the query with A, B, Cs instead of XXX for all cases? So that we can see the complete logic without any proprietary naming? Thank you!

[M Amine]

try playing with "... changed ..." like " status changed before currentLogin()" and combine it with jql functions about groups and roles. 

[Fabienne Gerhard]

Maybe I'm a little bit late but I got one of my first ones here for you that took me a little bit of time to get the right tickets out of nearly 250 and two projects on one board

(project = "Insane Relationship (INR)" AND "Epic Link" in (INR-2, INR-37, INR-3, INR-34, INR-50, INR-1, INR-9, EMPTY, INR-42)) OR (project = "IR Supporting" AND labels = related_INR) ORDER BY Rank ASC

 Other easy one:

project = AINR42 AND (assignee in(me, you, the other one) OR reporter was in (me, you, the other one) OR watcher in (me, you, the other one))