Durgesh Durgesh - 22 days ago 11
reST (reStructuredText) Question

SharePoint Online: REST API for List, calling Filters and limit the return items

I am trying to call REST API for list using below REST API

https://myweb.sharepoint.com/teams/sites/subwebs/_api/web/lists/GetByTitle('MyList')/Items?
$top=1
&$orderby=ID
&$select=ID,FName,LName,Title
&$filter=Title eq 'Female'


I need $filter should work with number of records limited to $top. The $top works if $filter is not applied.

Well, my list contains the items more than 5000. I received below error message while making GET Request for above URL

{
"readyState": 4,
"responseText": "{\"odata.error\":{\"code\":\"-2147024860, Microsoft.SharePoint.SPQueryThrottledException\",\"message\":{\"lang\":\"en-US\",\"value\":\"The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.\"}}}",
"responseJSON": {
"odata.error": {
"code": "-2147024860, Microsoft.SharePoint.SPQueryThrottledException",
"message": {
"lang": "en-US",
"value": "The attempted operation is prohibited because it exceeds the list view threshold enforced by the `enter code here`administrator."
}
}
},
"status": 500,
"statusText": "Internal Server Error"
}

Answer

The exception Microsoft.SharePoint.SPQueryThrottledException is thrown since $filter=Title eq 'Female' query causes to walk through the whole list and check every row to see if it matches.

According to MSDN:

The list view threshold does not apply simply to the number of results returned by your query. Instead, it restricts the numbers of database rows that can be accessed in order to complete execution of the query at the row level in the content database.

That's why $top query option is not applicable here.

One option to avoid the issue would be indexing the Title field.

Go to List Settings -> Indexed Columns -> Create a new index -> select Title as a Primary Column:

enter image description here

Once Title field is indexed, the following query should succeed:

https://site/_api/web/lists/GetByTitle('<list title>')/Items?$top=1&$orderby=ID&$select=ID,Title&$filter=Title eq '<value>'
Comments