It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

David David Jun 26, 2012

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?

8 answers

1 accepted

8 votes
Answer accepted
Marius Balčytis Jun 22, 2016

 

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..

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


Andrey Aristarkhov Jun 27, 2016

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 <= 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

Like Filip-David Molnar likes this
Trevan Householder Aug 19, 2016

That worked for me!  Thank you, Andrey!

Maximilian Machedon Oct 12, 2017

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.

Maximilian Machedon Oct 12, 2017

I've also tried a direct query via jira-cli and I also get an error:

 

jira-cli view --search-jql 'project = Development AND NOT (team IS EMPTY OR team IS NOT EMPTY)'

<h1>Page unavailable</h1>
<div class="aui-page-notification-description">
Please check <a href="https://status.atlassian.com">Atlassian Status</a> for any known issues.
<br />
If your page hasn't appeared again in 5-10 minutes, please contact our support team.
</div>
<br>
<p>
<a href="https://status.atlassian.com" class="aui-button aui-button-primary" style="width: 175px;">
Atlassian Status
</a>
&nbsp;
<a href="https://getsupport.atlassian.com/servicedesk/customer/jst" class="aui-button">
Contact support
</a>
</p>

Jose Ramirez Oct 26, 2018

May I down vote this ? As mentioned in the follow up comments, this is not a reliable solution.

Samer Harb Oct 26, 2018

@Jose Ramirez Did you try to re-index the project? There's a "Re-index project" button that solved a similar problem i faced

Jose Ramirez Oct 26, 2018

I did, but I'll try it again to make sure.

Jose Ramirez Oct 26, 2018 • edited

To elaborate, I recently added a custom field, and had to retroactively populate the field with data for issues. So when I scan for project=ABC AND custom field = EMPTY for a particular issue type, it has nothing, and returns nothing, obviously. The system knows nothing of the newly added field. I have to "JQL it" using only "project=ABC AND issuetype = XYZ" and then add the new field column on the issue listing, and order it so I can see the empty field for older issues, and then bulk change with a script in a post function to perform the retro mapping. It would have been great had the system recognized the 'emptiness' of the new field, but that would require a system re-index, and who has time for that ? 

Samer Harb Oct 29, 2018

 

@Jose Ramirez Hi again, sorry to hear about that. I am not familiar with problem unfortunately. Usually we make system re-index during maintenance times, outside of working hours when there's low traffic.

Btw you made sure that the field is searchable from the edit menu right?

2018-10-29 08_58_11-Edit Custom Field - Details - MxJIRA.png

Good luck!

9 votes
Izaskun Nazabal May 05, 2013

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

"CustomField" is EMPTY

Prakhar Srivastav May 24, 2016

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 

2 votes
Sam Huawey Dec 20, 2015

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).



Ajar Vashisth Jun 22, 2018

That worked . thanks

Александр Романов I'm New Here Jan 28, 2019

Worked in my case too. Thanks!

1 vote
Sascha Schwegelbauer Feb 08, 2015

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

1 vote
Samer Harb May 17, 2018 • edited

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

 

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

0 votes
Jobin Kuruvilla [Go2Group] Community Leader Jun 26, 2012

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!

Daniel Standish Jun 11, 2014

Izaskun's answer worked for me.

Robert Mar 11, 2017

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

Like Richard Cross likes this
0 votes
Daniel Standish Jun 12, 2014

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

0 votes
Shengyu Chen Nov 21, 2018

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

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Jira

Try Jira Cloud for Outlook: Organize your work without leaving your inbox

Hi Atlassian community, My name is Max and I work on the product integration team at Atlassian. I am pleased to announce the early access program for the Jira Cloud add-in for Outlook. This add-in...

2,326 views 6 15
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you