How To: Search up items which don’t have a value set

One of the harder things in search is to search for something which don’t have a value set, and it sort of goes against how a search engine works. If an item is missing a value on a property, then this property is not set in the search index. It will be non-existent.

Using SQL you can use operators such as IsNull to return items which are missing a value, but using SharePoint search you don’t have this operator.
A common work around is to provide some dummy/default value, and you can filter on this one instead. But often this is not an option.
Previously in my post KQL – The Basics I showed how to return all items which don’t have a value set on a taxonomy column, where you do a negative filtering on the term set GUID.

-owstaxIdLocation:"GTSet|#fc01ae6d-8ed3-4872-9cef-d2199d52d61c"

And the negation is the clue as to how you can achieve searching for items missing a value. If you know all possible values, or some part which all items which have the value set contain, then you can negate.

The current solution I was working on had a list of items with an optional start and end date. I wanted to return all items within the start and end dates as well as those with no dates set.
First of all, in order to filter on dates you have to map the crawled property for your columns to managed properties of type Date, for example RefinableDateXX.

In my list I have two site columns, StartDate and EndDate, with the corresponding crawled properties ows_StartDate and ows_EndDate. I map each of these to RefinableDate00 and RefinableDate01.

The query to limit items between these two dates is pretty straight forward:

RefinableDate00<=today AND RefinableDate01>=today

[Update 2014-12-11]
In order to also return values which don't have those dates set I switch the query around to

NOT(RefinableDate00>today) NOT(RefinableDate01<today)

Doing the negation will not only match on dates outside the start/end range, but also include empty values.

In order to return values without the date fields set I make use of one of the auto-generated managed properties for site columns – and using site columns is key here unless you want to create or map more managed properties yourself.

In my case I’m using EndDateOWSDATE. This property is of type text and will contain a value similar to 2014-08-27T22:05:00Z. Examining the date, the one character/word/term present in all dates is the capital letter Z. Modifying the above query you now get
(RefinableDate00<=today AND RefinableDate01>=today) OR –EndDateOWSDATE:Z
where you want all items between the start/end date or items which don’t have the letter Z in EndDateOWSDATE, which matches also match items with no date set.

Summary
Using the above queries you can now, without any custom coding, return items which are missing either taxonomy or date values.