SharePoint REST API List View Threshold

Question

I have a SharePoint List with over 5000 records created by me. The below query when using ID in filter works. But when I Filter With CreatedBy me it gives me List View Threshold Error.

Error:

    _api/Web/Lists/GetByTitle('TestRecords')/Items?$select=ID,Title&$filter=Author/EMail eq 
    '[email protected]'

Works:

    _api/Web/Lists/GetByTitle('TestRecords')/Items?$select=ID,Title&$filter=ID gt '5'

Both the above query returns more than 5000 records. But when using ID it works and gives 100 records. When filtering with Author it gives threshold error. I even indexed the CreatedBy Column and it still gives Threshold error. Why does it not return only 100 records?

Answer 1

SharePoint rest api limit result to 100 by default, you can’t return more than 5000 items once even you query based on indexed column. If your result of "Author/EMail eq '[email protected]'" over 5k , It will give Threshold Error even with index. We can use “$top” to retrieve more than 100 items but up to 5000 like following sample

/_api/web/lists/getbytitle('myList')/items?$top=1000

If you want to retrieve items more than 5000,Please refer to following code

 var url = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('DocumentList')/items?$top=1000";
    var response = response || [];  // this variable is used for storing list items
    function GetListItems(){
        $.ajax({
            url: url,  
            method: "GET",  
            headers: {  
                "Accept": "application/json; odata=verbose"  
            },
            success: function(data){
                response = response.concat(data.d.results);
                if (data.d.__next) {
                    url = data.d.__next;
                    GetListItems();
                }
            },
            error: function(error){
                   // error handler code goes here
            }
        });
    }

Answer 2

In order to successfully filter by Author properties you have to request expansion of those properties in the URL.

items?$select=Id,Title,Author/Id,Author/Email&$expand=Author/Id&$filter=Author/EMail eq '[email protected]'

&$expand=Author/Id is the "trick".

I think when you do not expand Author properties SharePoint act like filter is not applied and trying to retrieve all items it falls on the List View Threshold Error.