Search nugget - Grouping property restrictions within a KQL query

Figured I’d alert you guys to a small nugget I was made aware of, and which I have officially documented at https://docs.microsoft.com/en-us/sharepoint/dev/general-development/keyword-query-language-kql-syntax-reference#grouping-property-restrictions-within-a-kql-query.

The gist of it all is that you can now use parenthesis on property queries.

<Property Name>:(<Expression>)

As an example:

author:"John Smith" AND author:"Jane Smith"

can be rewritten as:

author:("John Smith" "Jane Smith")

The other cool side effect of adding () around the property value is that the term(s) will be lemmatized. The query title:page return matches with the exact term page only, while title:(page) also return matches for the term pages.

Head over to the docs to read all about it and check out some more complex samples. Start adding those parenthesis, and may the search be with you!

Note: Works for SharePoint Online and SharePoint 2019 only.

An approach to search for a URL within a page or document in SharePoint

The Search Explained Yammer network is a great place to ask the weird and quirky search questions. Unfortunately it’s not indexed by Google, so answers there will not benefit everyone – which is why I’m writing this post.

A couple of days ago someone posted a question if it was possible to find pages in SharePoint which contain a specific link. The need was to identify broken links. Use-case can be if you rename a file, and want to find all pages linking to that file before renaming – to make sure they still point correctly.

This post will show you one approach which works, and if you have a better suggestion, please let me know.

Question is; how can you go about finding a page with the link https://contoso.sharepoint.com/sites/collection/site/library/document.docx as part of the content?

How to do “starts with” in KQL

When I wrote KQL – The basics back in 2014 I forgot to cover how you can achieve starts with for text property queries. It’s not an operator per say, as it combines equals with quotes and wildcard.

To find all items where title starts with Mikael, you can write:

title="mikael*"

Note that title="mik*" will not work, you need to type full terms before the wildcard for this to work, but still quite useful. I have also updated the original post to cover this.

Limit search results to “Documents”

An often asked question is to have Document Search, where the want is to list documents only - typically Office type formats like Word, Excel and PowerPoint. But the exact answer is not that white/black, and can differ from company to company.

One query you might consider executing to list documents only in SharePoint is IsDocument:true.

This query will return everything SharePoint deems a file or web page.. or not a list item (almost), returning Office documents, images, zip files… you name it, it will be part of the returned result set.

Which brings up the question: What is a document?

How to remove certain items from a list/library in SharePoint, and keep all other items

The scenario is as follows. You have indexed a SharePoint farm and one of your business owners tells you that in his library he keeps multiple copies of a file, where the latest is marked as ApprovalStatus=1 with a custom column. And only those files should be surfaced in the search results.

So, how do you craft search a query? At first you might write something like:

ApprovalStatus=1

This will exclude all items which don’t have that value as well, so not a good idea.

Next you know the list has a custom list definition so you try something like:

ApprovalStatus=1 AND ContentClass=STS_ListItem_10300

Now you’re excluding all other content except that list. Even worse, but still explicit to what you want. The solution is to add an OR clause to include all the other stuff you want, which is everything.

((ApprovalStatus=1 AND ContentClass=STS_ListItem_10300) OR (ContentClass<>STS_ListItem_10300))

And we have arrived at the final query. It will include anything not from the 10300 list definition + items from the 10300 definition which is approved.

Conclusion

When you want to exclude certain content, it’s much easier to generate a rule saying what you want to include. In this case one part specifying the exact rule for what to show for a particular library, and one part saying everything else except that library.

Other possible solutions

• Content enrichment web service which excludes content from that library and which is not approved
• Don’t keep multiple files, but use SP versioning
• Move non-approved documents to a library which is not being indexed

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.