I am trying to write an advanced search query that searches for a datetime field in an unset state.
I’m trying to make the following search work on a custom datetime ticket field:
custom_date:NULL
I have found a work around to do what I want, but it’s not ideal - the following works fine:
!custom_date:[1900-01-01 TO 2100-01-01]
The work around catches null datetime values by discounting any date/time that looks valid - but it would be better to search directly for something like NULL.
I have the same issue with single selection fields. I have to check that none of the potentially many possible field are selected.
If I have a single selection field called resolve with possible values of (nil, resolved, unresolved) I would think that I could do something like:
!resolved:resolved to catch both unresolved and nil values.
However, the only way that I can find that works is checking for “!resolved:resolved AND !resolved:unresolved”
You cannot achieve what you’re trying to do due to Elasticsearch limitations:
Especially the first sentence is relevant here:
A null value cannot be indexed or searched. When a field is set to null , (or an empty array or an array of null values) it is treated as though that field has no values.