REST query not able to filter by FileRef, works in on-premise but not online

Question

I have a REST query which works as it should in SharePoint 2013 on-premise, but not in SharePoint Online.

The query is as follows (after encoding):

https://<tenant>.sharepoint.com/projects/1/_api/web/Lists(guid'37D38739-88BF-4EC4-B188-F334F88E2566')/Items?$expand=File&$select=FileRef,File/ServerRelativeUrl&$filter=startswith(FileRef,%27%2Fprojects%2F1%2FShared%20documents%2Ftestfolder1%2F%27)

If I remove the filter, I see all my files and folders in the result as expected. As soon as the filter is turned on, I get nothing. I can see from the the result when I don't include the filter that several items have the value I am attempting to filter on. Changing the filter to FileRef eq 'specific value' yields no results either.

Is it not possible to use filter on this field in SharePoint online?

Answer 1

This issue is fixed in April 2018 CU of SharePoint server 2016.

Answer 2

Indeed, $filter query option does not seem to be working as expected in SharePoint Online/SharePoint 2016 with at least the following fields:

• FileRef
• FileLeafRef
• FileDirRef

The similar issue has been already reported in User Voice community but unfortunately without any feedback so far.

Meanwhile you could utilize GetItems method to construct a filter expression via CAML query instead of $filter query option as demonstrated below:

function getListItems(webUrl,listTitle, queryText) 
{
    var viewXml = '<View><Query>' + queryText + '</Query></View>';
    var url = webUrl + "/_api/web/lists/getbytitle('" + listTitle + "')/getitems"; 
    var queryPayload = {  
               'query' : {
                      '__metadata': { 'type': 'SP.CamlQuery' }, 
                      'ViewXml' : viewXml,
               }
    };

    return $.ajax({
           url: url,
           method: "POST",
           data: JSON.stringify(queryPayload),
           headers: {
              "X-RequestDigest": $("#__REQUESTDIGEST").val(),
              "Accept": "application/json; odata=verbose",
              "content-type": "application/json; odata=verbose"
           }
     });
}

Usage

var queryText = '<Where><Contains><FieldRef Name="FileRef"/><Value Type="Url">site/web/library/folder</Value></Contains></Where>';


getListItems(_spPageContextInfo.webAbsoluteUrl,listTitle, queryText)
.then(function(data)
{
     var items = data.d.results;
     for(var i = 0; i < items.length;i++) {
         console.log(items[i].Title);
     }    
})
.fail(function(error){
    console.log(JSON.stringify(error));
});