How can I search for a null value?

[Ryan Eames (Admin)]

As the subject states, I would like to search for tickets that have a null value in a particular field.  For example, I want to find all tickets that have no labels, or no fix version, etc.

In the particular case I was using it, I have a search that will find all tickets without 3 specific labels, but when it returns a result, it is basically every other ticket that has a label, just not those three.  This is only partially correct in the result I want, but I also need to see all the tickets that have no label to make my result truely complete.

[Nic Brough · Accepted solution]

For a simple null value in a field, try

• labels is empty
• fix version is empty

and so-on.

The question about "not label" is a bit more of a faff, because of the way humans tend not to be precise in their logic.  "Label is not X" does not mean the same as "label is empty", it means "there is a label on the issue, but it is not X"

When you are looking for your issues that do not have 3 specific label, you need to say "Label not in ( x, y, z ) or label is empty"

[Ryan Eames (Admin)]

Thanks, that helps, I didn't realize there was an EMPTY modifier as it does not populate in the JQL line as an option.

Cheers!

[Nic Brough]

It does pop into the list, but only when you've used specific phrases.  Later versions of JIRA have improved it a bit, widening the phrases that trigger it.  6 is ok,  but 7 offers it more and more.

[petar Topchev]

Hey Nic,

Here we use Jira v7.2.7

Whenever I try to execute the following query: 

project = P1 AND (labels not in (Test_Issue) or labels is EMPTY)

I get no results back, even though there are over 30 issues without a label in that particular project. Any idea what might be the problem. (I have permission to view the issues in that project.)

[Nic Brough]

Try simplification - does "project = p1 and labels is empty" return the ones you expect?  (plus others, of course)

[Petar Topchev]

Hey Nic, thank you for the answer. I don't know when I did your query first (without test_issue) it showed the desired result. Then I ran my query again and it seemed to have worked

[Petar Topchev]

Spoke too soon...

We have 384 issues in our project of which 380 have no labels. When I do the Labels is empty it only returns me 54 issues. The issue-types all use the same field scheme and workflow. And it didn't return any issue type in particular but all randomly...

There is no issue security set

[Nic Brough]

That sounds like a broken index! 

Could you try re-running the filters, note how many issues each one returns (don't worry about the content, just numbers), then re-index the project and re-run and not if any of the numbers change?

[petar Topchev]

Thank you Nic, that fixed it!