JQL filter for empty AND filled field

sour_soeren August 10, 2020

 Hi there,

I'm trying to create a JQL filter inside one project, to filter all issues with an "empty" "assignee" AND with "assignee" "not in" (username, username).

 

But the following statement doesn't work:

project = "ABC" AND Sprint in (347, 331) AND assignee not in (username, username, username, username) AND assignee = EMPTY AND status not in (done, Storno) AND status not in (Done, Resolved) ORDER BY assignee ASC, cf[10004] ASC

What' wrong?

Thanks in advance.

1 answer

1 accepted

3 votes
Answer accepted
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 10, 2020

You've defined a query that can not possibly return anything.

Just looking at the bit that causes the problem, and shortening it a bit), have a think through what this really says:

assignee not in (A, B, C, D) and assignee is empty

There's two possibilities there, the assignee is either filled with something, or it is empty.  If you AND those, you're always going to get nothing because it cannot be both values at the same time.

I think your query should be:

project = "ABC" AND Sprint in (347, 331) AND

( assignee not in (username, username, username, username) OR assignee is EMPTY )

AND status not in (done, Storno, Done, Resolved)

ORDER BY assignee ASC, cf[10004] ASC

sour_soeren August 10, 2020

That was fast :)

Thank you very much and you're absolutely right, but sometimes you need a different person to check the work.

Like Nic Brough -Adaptavist- likes this
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 10, 2020

I know what you mean.  JQL queries are one of the things I often need someone else to check!

Like # people like this
J_R_ Menzinger September 16, 2022

Hi Nic, 

but... what I found today is that if you filter for a property not being something (and the property can be empty) then the filter will not match the empty ones.

for example:

assignee != UserName 

will return less results than 

assignee != UserName  OR assignee is EMPTY


And this behaviour will also occur for labels , comments , etc.

labels != LabelName 

labels != LabelName OR labels is EMPTY

 

Comment !~ "WordToExclude"

Comment !~ "WordToExclude" OR comments is EMPTY

 

Is this a bug on JIRA?

Like # people like this
Darren Griffith April 20, 2023

Does anyone know if Atlassian has something like an ISEMPTY(assignee, "") = "Jane Smith" in the works? Something that works like ISNULL or NVL in the SQL languages.

Like Jesse Berkeley likes this
twhalen September 21, 2023

@J_R_ Menzinger - it appears to me to be a bug as well.  I always have to include "Or X = Empty" otherwise I don't get the respected results.  Seems pretty wonky.

Like J_R_ Menzinger likes this
Aravinth
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
March 27, 2024

( ) These brackets saved my day. Thanks for the help. 

Like Nic Brough -Adaptavist- likes this

Suggest an answer

Log in or Sign up to answer