Thursday, July 18, 2013

Limiting search results by exact time in SharePoint 2013–and how to do FQL

[Update 2020-01-27]
KQL in SharePoint Online and SharePoint 2019 now support time portion as well. For SP2016 and SP2013 you still need to use FQL to accomplish this.

[Original post]
Often you might find the need to limit search results between two dates, or even more exact, between two exact time intervals. The issue is that property queries written in KQL will disregard the time portion of your query, limiting to full day results only (See this old post for an explanation).

FAST Search for SharePoint allowed queries with the time portion in Sharepoint 2010 by using FQL, and fortunately you can do this in 2013 and Office 365 as well.

Doing FQL in 2013 in theory involves setting a parameter either on your REST queries or on the KeywordQuery object telling it your query is written in FQL and not KQL. The reality is a bit more complex involving a custom result source, and to tell you the truth. I have yet to be able to get FQL to work using REST this way.

How then? you might ask. Use use the RefinementFilters property instead! This property actually uses FQL which is what we need and is our “Get out of jail free card”. And all though KQL has more of the FQL operators in 2013, FQL still has some tricks up it's sleeve. Worth taking a look at for sure.

To limit on dates you will use the FQL range operator. The default behavior is to use “greater than” and “equal and less than” if not specified in the query.

The granularity of date/time queries are down to 7 decimals, as long as the crawler has been able to set this level of granularity during indexing.
Below are some sample REST queries limiting the results based on time:
// Return all results greater than or equal to last modified time of 2013-07-17T12:35:57
/_api/search/query?querytext='*'&refinementfilters='write:range(2013-07-17T12:35:57.0000000Z,max,from="ge")'

// Return all results greater than last modified time of 2013-07-17T12:35:57
/_api/search/query?querytext='*'&refinementfilters='write:range(2013-07-17T12:35:57.0000000Z,max,from="gt")'

// Return all results less than or equal to last modified time of 2013-07-17T12:35:57
/_api/search/query?querytext='*'&refinementfilters='write:range(min,2013-07-17T12:35:57.0000000Z,to="le")'

// Return all results less than last modified time of 2013-07-17T12:35:57
/_api/search/query?querytext='*'&refinementfilters='write:range(min,2013-07-17T12:35:57.0000000Z,to="lt")'

// Return all results between and including 2013-07-17T12:35:57.0000000Z and 2013-07-17T13:35:57.0000000Z
/_api/search/query?querytext='*'&refinementfilters='write:range(2013-07-17T12:35:57.0000000Z,2013-07-17T13:35:57.0000000Z, from="ge",to="le")'


It’s also possible to filter on other properties like Author, with starting-with, and ends-with:

/_api/search/query?querytext='*'&refinementfilters='author:starts-with("Mikael")
/_api/search/query?querytext='*'&refinementfilters='author:ends-with("Svenson")

If you want to boost your query using FQL, you may write something like this:

// Items with the term "test" will get 100 extra boost points
// Items with the terms "test" and "developer" will get 200 extra boost points
/_api/search/query?querytext='*'&refinementfilters='xrank(xrank("*","test",cb=100),"developers",cb=100)'

Pay attention to the use of “*” in the matching part. This means it will evaluate all results based on the initial query value, and boost accordingly. If you enter a term instead of “*” in the refinementfilter, you will also limit the actual results.

Want more FQL samples? Take a look at my book. Working with FAST Search Server 2010 for SharePoint.