Tuesday, April 25, 2023

Filter on managed properties in search with or without values

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:

https://learn.microsoft.com/en-us/sharepoint/dev/general-development/keyword-query-language-kql-syntax-reference#filter-on-items-where-a-text-property-is-empty-or-contains-a-value

Note: The new supported syntax only works for Microsoft 365 / Online

  image

 

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