Finding unassigned issues in multiple projects and fields

Question

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- · Answer

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.

Rachael Gardner · Answer

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- · Answer

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