Finding unassigned issues in multiple projects and fields

[Rachael Gardner]

I am working in an area that receives tickets from multiple Jira projects due to a recent merger of teams. In addition, there are over 13 individual fields within these tickets that can identify it is work for my team. In order to work around it we have a JQL that pulls in tickets by project type and assignee. The issue is that when we use 'assignee = EMPTY' to pull in unassigned tickets it will pull all unassigned tickets for the entirety of all projects. Is there a good way to pull these in other than creating a JQL with each possible project and field type? 

[Nic Brough -Adaptavist-]

Welcome to the Atlassian Community!

That sounds like you've drifted into an unmitigated mess when you've got 13 fields to look at.  You should really have at most three - the assignee, a team indicator, and a specific label. 

This lets you use simple filters, consistent ways of doing things, and has the flexibility to work across projects and teams.  A lot of places I've worked have JQL for their boards which is "<whatever identifies the team> OR label = <team name>" and sometimes "assignee in team" type queries

So, no, I am sorry that your assumption is correct, you are going to have to build a klunky filter for it, but it's not too hard to work it out.  You'll need

Assignee is empty and (field 1 = X or field 2 = X or field 3 = X)   

and so on for every field you've been clobbered with.  Obviously, replace the X with whatever your team indicator is for that field.

[Rachael Gardner]

Thanks, Nic! Based on your feedback I changed my query to something like: 

project = x and status not in (a, b, c) AND assignee in (a, b, c) AND assignee is EMPTY AND field1 = X OR field2 = X OR field3 = X 

... and combining the other projects with -OR-. For some reason it is still pulling in tickets from those fields with assignees and in statuses that I have filtered out. Any clue why that might be happening? 

[Dwight Holman]

Looks like an operator precedence issue. You'll need to put the OR clauses into braces:

project = x 
AND status not in (a, b, c) 
AND assignee in (a, b, c) 
AND assignee is EMPTY 
AND ( field1 = X OR field2 = X OR field3 = X )

(I've added newlines here for readability)

[Nic Brough -Adaptavist-]

Neither of the above will work.

In the first one, you have not told Jira how to read the query, you've ignored the parentheses I put in my example.  It has no choice but to read left to right.  It's fine until you swap to the OR.  Your query will execute up to or field1 = X, but then the rest of it is OR field2 OR field 3.  So those parts are selecting for all issues where field2 or field3 are X.

The layout in the second query is correct, but it will now return nothing because you are asking it to say

Assignee is empty AND assignee in (a, b, c)

It can't be a, b, or c, AND be empty.

Try this:

project = x and status not in (a, b, c) AND (assignee in (a, b, c) OR assignee is EMPTY) AND (field1 = X OR field2 = X OR field3 = X)

Note exactly what clauses the parentheses are surrounding, and that I've changed the assignee clause to an OR

[Rachael Gardner]

Getting closer, thanks for helping me through it! It's still pulling in assignees that aren't indicated in the query. Here's a redacted view below of the actual query:

project = X AND status not in (X, X, X, X) AND (assignee in ("X", "X", "X", "X", "X", "X", "X", "X", "X", "X", "X", "X") OR assignee is EMPTY) AND ("X" = "X" OR "X" = "X" OR "X" = X OR "X" = "X") OR project = "X" AND status not in (X, X, X, X AND (assignee in ("X", "X", "X", "X", "X", "X", "X", "X", "X", "X", "X", "X", "X", "X", "X") OR assignee is EMPTY) AND ("X" = X OR "X" = X OR "X" = X OR "X" = X OR "X" = X OR "X" = X) ORDER BY assignee ASC

It looks like it's pulling in tickets with the identified fields that do not belong to the assignee list. Do I need to exclude those users? 

[Nic Brough -Adaptavist-]

Your parenthesis are still missing.

By the way, the way I tend to check is the same as what Dwight did, list out the joined clauses by line, but also, I indent to make it clear where the joins are.

The important thing to remember is that a query builds a list of issues.  If you only use AND clauses in the top level query, then you are building a single list.  If you use OR, then each clause is building its own list, and adding to the main list.

In your query, it looks fine until the top-level OR kicks in.  You have not told Jira to accumulate the ANDS following it to accumulate with the project, it is ORring project = X

What you need is 

Project = X and (the status bit) and (the assignee bit) and (the field bit) OR ( Project = X and (the assignee bit) and (the field bit) )

To make it even more clear to a human, I would surround the first half of the question too:  

(Project = X and (the status bit) and (the assignee bit) and (the field bit) ) OR ( Project = X and (the assignee bit) and (the field bit) )

makes it 100% clear that there are two independent lists being built.