Back in 2014 I wrote the post How To: Search up items which don’t have a value set, which covers how to write keyword query syntax (KQL) filters to return or restrict items depending on if a specific managed property has a value or not. Recently Microsoft added support to more easily query if managed properties of type Text contain or does not contain a value.
Here’s a link to the updated documentation:
Note: The new supported syntax only works for Microsoft 365 / Online
Items missing or having a text value
The syntax is as follows:
| KQL Syntax | Description |
| NOT <Property Name>:* | Items where a property does not have a value |
| <Property Name>:* | Items where a property does has a value |
The documentation uses the following example to list SharePoint sites associated to a hub site.
(DepartmentId:* OR RelatedHubSites:*) AND contentclass:sts_site NOT IsHubSite:true
Deciphering the query:
| KQL | Description |
| (DepartmentId:* OR RelatedHubSites:*) | return items which has a value in the original DepartmentId managed property or in the successor RelatedHubSites property |
| contentclass:sts_site | return only site items |
| NOT IsHubSite:true | exclude hub site results |
Note that hubs connected to another hub will not be included in the above query. If you want those, then remove the NOT IsHubSite:true part and post-process the results as needed.
For completeness let’s cover how to accomplish the same for other types of managed properties.
Items missing or having a YesNo value
To find items missing a value in a date property the syntax shown in my 2014 seems to no longer work and should be replaced with the following where the date is some low non-used date.
| KQL | Description |
| NOT (RefinableYesNo00:true OR RefinableYesNo00:false) | return items not having a value in a YesNo property |
| (RefinableYesNo00:true OR RefinableYesNo00:false) | return items having a value in a YesNo property |
Items missing or having a date value
To find items missing a value in a date property the syntax shown in my 2014 seems to no longer work and should be replaced with the following where the date is some low non-used date.
| KQL | Description |
| NOT RefinableDate00>1900-01-01 | return items not having a value in a date property |
| RefinableDate00>1900-01-01 | return items having a value in a date property |
Items missing or having a number value
For number type managed properties it’s easier as you typically know the range of values.
| KQL | Description |
| NOT Size>=0 | if the managed property only contain positive values, then this will return all items with no value set |
| NOT RefinableDecimal00>=0 NOT RefinableDecimal00<0 | return items where the property RefinableDecimal00 has no value |
| Size>=0 | return all items having a value which is greater than your smallest value |
