how to filter fields using contains in sharepoint search api

Question

I was using Sharepoint Rest api and the query looked like the one below

http://sitename/HR/_api/web/lists/GetByTitle('Employee')/Items?$top=4000&$filter=( substringof('100',Title) and substringof('ARISPL',Company) and substringof('HIBIS',House_x0020_Name))

Now I want to migrate to Search Api as querying against Note fields is not possible in Rest API

I was able to get items from the list with below query

http://sitename/HR/_api/search/query?querytext='Path:http://sitename/HR/Lists/Employee'

but I am not sure how to add filters against fields with Contains and a and condition

I tried the below query but it didnt work

http://sitename/HR/_api/search/query?querytext='Path:http://sitename/HR/Lists/Employee'&refinementfilters='AND((Department:mvrla)(Title:1000))'

Can someone help?

I want to convert the above rest api query to search api query

Answer 1

You should try out the SharePoint 2013 Search Query Tool. It will help you craft your query and show you the results.

You can use the colon as a contains operator :. In KQL AND is the default behavior between terms. I think your query would be something like: querytext='Path:http://sitename/HR/Lists/Employee/* Department:mvrla Title:1000' which should be the same as: querytext='Path:http://sitename/HR/Lists/Employee/* AND Department:mvrla AND Title:1000'

Answer 2

There is no contains filter

• URI Conventions (OData Version 2.0)
  http://www.odata.org/documentation/odata-version-2-0/uri-conventions/

• Use OData query operations in SharePoint REST requests
  https://msdn.microsoft.com/en-us/library/office/fp142385.aspx

Search has different syntax

• SharePoint Search REST API overview
  https://docs.microsoft.com/en-us/sharepoint/dev/general-development/sharepoint-search-rest-api-overview

• FAST Query Language (FQL) syntax reference
  https://msdn.microsoft.com/en-us/library/office/ff394606.aspx

Answer 3

The KQL query to use AND operation is as follows

<<siteurl>>/_api/search/query?querytext='Path:Path:http://sitename/HR/Lists/Employee (Department:mrvla) (Title:1000)'

You should enclose in double quote if you want exact match and space will treat as AND operation.

The above code blocks works as Path starts with given value and Department starts with mrvla and Title starts with 1000. There is no contains operation in a word.

Here is a MSDN article to know the keyword search options.

https://msdn.microsoft.com/en-us/library/office/ee558911.aspx

Thanks, Venkat Konjeti