Can someone help me figure out a JQL issue?

[Philip Culver]

I use the query below for one of my teams:

project in (FP, FNA) OR labels in (fp, fna) and labels not in (not-active) ORDER BY Rank ASC

It returns 2,456 results. That query returns an issue in the FP project that has the `not-active` label on it. I would have thought the `labels not in (not-active)` piece would have excluded the issue from the list.

A coworker suggested I wrap the first part of the query in (). I tried that, and the results had 1300 less issues in it, including 11 fewer issues in the active sprint. So, that's not an option. 

Does anyone have any other ideas? I've used the `labels not in ()` before, and it hasn't been an issue. 

[Nic Brough -Adaptavist-]

Always use parenthesis to explain to the humans what the query actually is (unless all of the clause joins are the same - all AND or all OR - we don't need help with those).  It's good practice to do it with the computers as well, as humans are really bad at understanding how a computer will read an non-parenthesised query left to right.  I certainly get it wrong every time.

Working down your list:

• If an issue is in FP project, I want it on the list.
  • Project = FP
• If an issue is in FNA project, i want it on the list.
  • Project = FNA
• If it's in Project X, Y, or Z but has either the fna or fp label, I want it on the list. 
  • Project in (X, Y, X) and labels in (fna, fp)
• But if ANY issue that matches any of that criteria, but has the not-active label, I want it excluded from the list. 
  • label != not-active

Combining one clause at a time in that order

• Project in (FP, FNA)
• Project in (FP, FNA) OR Project in (X, Y, X) and labels in (fna, fp)
• ( Project in (FP, FNA) OR Project in (X, Y, X) and labels in (fna, fp) ) AND label != not-active

[Tim Perrault]

Voting for @Nic Brough -Adaptavist- cause it looks more advanced (cooler) and he explained it better :)

[Nic Brough -Adaptavist-]

Except I missed something.

It should be

labels != not-active OR labels is empty

"labels != not-active" will not return issues with no labels because there's nothing to run the "not equal to" logic against.

So...

• ( Project in (FP, FNA) OR Project in (X, Y, X) and labels in (fna, fp) ) AND ( label != not-active OR labels is empty )

[Tim Perrault]

You're forgiven lol. It's hard writing JQL without being able to search on it instantly.

[Philip Culver]

Thanks for your help!

It's not working well, with the 'or labels is empty' or without it. Both of those queries end up excluding issues in the FP and FNA projects - even if they don't have the `not-active` label. 

I want to see anything in those projects (excluding issues with `not-active` label) and any issue in any project if it has either the `fna` or `fp` label (excluding any that have the `not active` label)

I think I'm going to change my approach and edit the workflow on the projects I mentioned to allow an On Hold status, and then filter out issues in that status, rather than depend on the `not-active` label.

[Tim Perrault]

Status is easier to filter on, but the JQL checks out. Is the label not-active always spelled that way? Would a user accidentally put in not active? I'm not the biggest fan of labels and prefer something like component where it's controlled a little better.

[Philip Culver]

I can't explain why it didn't work. We only have 35 issues in the entire system (which has a total of 125K issues in it) with that label on them. No matter how I edited the query, the number of matches would drop anywhere between 900 & 2000.

I did just add the On Hold status to the workflow that most of our projects use, and that did the trick. Now, instead of using that label at all, I can use On Hold to exclude issues from the board. 

Thanks for your help! 

I've used JQL for many years now, and I really have no clue why I couldn't get it to work on my own, or with the help of the community! 

[Tim Perrault]

Hi @Philip Culver 

Change the first OR to an AND

The query is finding all issues in those two projects. Then after OR it find all issues with labels fp or fna, but not ones with not-active.

The result is the all the issues in the projects + issues with certain labels, but not other label

Tim

[Philip Culver]

I tried that, but it doesn't make sense to me. The result was that it removed ~2,000 from the query.

Using AND there is telling the system that I want issues that are in either of those project AND they must have those first two labels, right? I'm looking for any combination of the two projects OR issues that are not in those projects that have one of the first two labels. 

Thanks for your help, but it's not exactly what I'm trying to get back from the system.

This is what I'm looking for:

• If an issue is in FP project, I want it on the list.
• If an issue is in FNA project, i want it on the list.
• If it's in Project X, Y, or Z but has either the fna or fp label, I want it on the list. 
• But if ANY issue that matches any of that criteria, but has the not-active label, I want it excluded from the list. 

[Tim Perrault]

project in (FP,FNA) AND labels not in (not-active) or labels in (fna, fp) and labels not in (not-active)