nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JQL query for issues where a custom field is empty or where the custom field isn't associated with the issue

David David

I have a situation where some issues in a project have a custom field, while others do not. If I perform a search like:

project = FOO and customField is EMPTY

This will only return the issues that have the custom field "customField" and where the field is empty, it will exclude all issues that aren't associated with the custom field "customField"

I'm trying to execute a query that will return all issues where either the custom field is empty, or where the custom field is not associated with the issue. Is there a way to do this?

Accepted answers

Marius Balčytis

I've found the solution, but it's really hack-ish.

Both customField IS EMPTY and customField IS NOT EMPTY does not return tickets that have no customField at all. So, basically, NOT (customField IS EMPTY OR customField IS NOT EMPTY) should return them. As it turns out, it doesn't. Probably some pre-processing of query etc. But if you save it as a filter, it works. So..

Query for customField IS EMPTY OR customField IS NOT EMPTY
Save filter as Has customField
Open new query window
Now if you search for NOT filter = "Has customField", you get issues that have no customField at all
Your final query should be project = FOO and (customField is EMPTY OR NOT filter = "Has customField")

All comments

Jobin Kuruvilla [Adaptavist]

That is not possible via JQL. You might want to write a plugin or find it from the database if it is a one time task!

Izaskun Nazabal

In our instalation this sentence it works. Try enclosing field name in quotes:

"CustomField" is EMPTY

dpstandish

Izaskun's answer worked for me.

dpstandish

The OP's query actually works now. You don't need quotes.

Sascha Schwegelbauer

I'd like to query for all issues that don't have a value in "Account" (custom field 10101).

"Account is EMPTY" returns all issues (not corrent) "cf[10101] IS EMPTY" also returns everything.

what's the correct way to do this search? JIRA is 6.4m13

Sam Huawey

I second that. The "custom field" is EMPTY does not always work. I think this is a bug in JIRA (mine is 7.0).

I tried to reindex search, but it did not help.

This is somehow related to a situation where you have two or more projects with same set of custom controls. E.g. prj1 contains "custom field" and prj2 also contains "custom field".

The correct jql should be the following:

project=prj1 and cf[10201] is EMPTY

, where 10201 is the id of your custom field (you may quess it if you add a search column with your CF value and inspect corresponding html code).

Prakhar Srivastav

Hi All,

I am also facing this issue while I do jql for Component field in a project.

Search1 : project= proj1 and "component" is not EMPTY

Search2 : project= proj1 and "component" is EMPTY

Search3 : project= proj1

For me

Search3 is not equal to some of Search1 and Search2.

Please advice.,

Regards

Prakhar

Marius Balčytis

I've found the solution, but it's really hack-ish.

Query for customField IS EMPTY OR customField IS NOT EMPTY
Save filter as Has customField
Open new query window
Now if you search for NOT filter = "Has customField", you get issues that have no customField at all
Your final query should be project = FOO and (customField is EMPTY OR NOT filter = "Has customField")

Andrey_Aristarkhov

Script Runer allows implement straigtforward solution with the only JQL query

project = FOO and issueFunction not in expression("project = FOO and (customField IS EMPTY OR customField IS NOT EMPTY)", "created &lt;= today")

It's also slightly hackish, since expression always means "all issues from subquery", but at least it could be done via scripting w/o filter being saved

Trevan Householder_Isos-Tech-Consulting_

That worked for me! Thank you, Andrey!

Robert69

Not possible without a plugin is the standard answer to every issue.

Maximilian Machedon

I've tried doing that just now (I hope I followed the steps correctly). On the last step (the final query) I get this error:

Error occurred communicating with the server. Please reload the page and try again.

Obviously, no results are returned.

Samer Harb

Use "Re-index project" to solve this issue once and for all

2018-05-17 14_35_34-Re-index - ITJIRA.png

ajarv

That worked . thanks

Shengyu Chen

Actually right now the is Empty is accepted and would return the results

Alexander Romanov_maxg6j

Worked in my case too. Thanks!

John Tian

We get results when searching 'is empty' but not all results for all tickets that don't have the field populated. I don't think this works as intended, but two of the above workarounds do the trick.

Marius's suggestion to make a filter and reference it
Andrey's suggestion to use ScriptRunner to do the same (only works if we reference cf[####] instead of the field name, since it has spaces in the name)