Failiure when trying to searrcha document library with may files

Question

I am trying to search items in a document library, using the following request

https://xxx.sharepoint.com/sites/NewTestMultiDrives/_api/web/lists(guid'6f499a58-db87-40cb-979c-ff85f496ce0f')/items?%24top=25&%24filter=substringof%28%27aaa%27%2C+FileLeafRef%29+and+startswith%28FileDirRef%2C+%27%2Fsites%2FNewTestMultiDrives%2FShared%2520Documents%27%29&%24expand=File%2Fproperties%2CFile%2CProperties&%24select=FileRef%2CFileLeafRef%2CFileDirRef%2CFileDirRef%2CFileSizeDisplay%2CProperties%2CFile%2CFolder%2C%2A

And i get this error, although there is no item with the searched name:
Error 500 - The attempted operation is prohibited because it exceeds the list view threshold.
I am attaching also the response headers, in case it helps:
OrderedDict([('cache-control', ('Cache-Control', 'private, max-age=0')), ('transfer-encoding', ('Transfer-Encoding', 'chunked')), ('content-type', ('Content-Type', 'application/json;odata=minimalmetadata;streaming=true;charset=utf-8')), ('expires', ('Expires', 'Wed, 24 Aug 2022 08:38:35 GMT')), ('last-modified', ('Last-Modified', 'Thu, 08 Sep 2022 08:38:35 GMT')), ('vary', ('Vary', 'Origin')), ('p3p', ('P3P', 'CP="ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI"')), ('x-sharepointhealthscore', ('X-SharePointHealthScore', '2')), ('x-sp-serverstate', ('X-SP-SERVERSTATE', 'ReadOnly=0')), ('dataserviceversion', ('DATASERVICEVERSION', '3.0')), ('spclientservicerequestduration', ('SPClientServiceRequestDuration', '32')), ('x-aspnet-version', ('X-AspNet-Version', '4.0.30319')), ('x-databoundary', ('X-DataBoundary', 'None')), ('x-1dscollectorurl', ('X-1DSCollectorUrl', 'https://mobile.events.data.microsoft.com/OneCollector/1.0/')), ('x-ariacollectorurl', ('X-AriaCollectorURL', 'https://browser.pipe.aria.microsoft.com/Collector/3.0/')), ('sprequestguid', ('SPRequestGuid', 'd1e662a0-904c-d000-510c-01ac34c3e362')), ('request-id', ('request-id', 'd1e662a0-904c-d000-510c-01ac34c3e362')), ('ms-cv', ('MS-CV', 'oGLm0UyQANBRDAGsNMPjYg.0')), ('strict-transport-security', ('Strict-Transport-Security', 'max-age=31536000')), ('x-frame-options', ('X-FRAME-OPTIONS', 'SAMEORIGIN')), ('content-security-policy', ('Content-Security-Policy', "frame-ancestors 'self' teams.microsoft.com *.teams.microsoft.com *.skype.com *.teams.microsoft.us local.teams.office.com *.powerapps.com *.yammer.com *.officeapps.live.com *.office.com *.stream.azure-test.net *.microsoftstream.com *.dynamics.com *.microsoft.com securebroker.sharepointonline.com;")), ('x-powered-by', ('X-Powered-By', 'ASP.NET')), ('microsoftsharepointteamservices', ('MicrosoftSharePointTeamServices', '16.0.0.22824')), ('x-content-type-options', ('X-Content-Type-Options', 'nosniff')), ('x-ms-invokeapp', ('X-MS-InvokeApp', '1; RequireReadOnly')), ('x-cache', ('X-Cache', 'CONFIG_NOCACHE')), ('x-msedge-ref', ('X-MSEdge-Ref', 'Ref A: 7C201909F05A4B2BBA2063DCA4D9C259 Ref B: ATH01EDGE0510 Ref C: 2022-09-08T08:38:35Z')), ('date', ('Date', 'Thu, 08 Sep 2022 08:38:34 GMT'))])

Can you provide some help on how we can overcome this issue?

Answer 1

Hi @Giorgos Poulis
Please check the items in the first filter less than 5000. If the first filter condition returning more than 5000 items, The attempted operation is prohibited because it exceeds the list view threshold will occur. This needs to always return less than 5000 items and only after that the other filter queries are applied. Here is a similar issue to yours for reference
https://sharepoint.stackexchange.com/questions/294120/sharepoint-rest-api-spquerythrottledexception

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Hi @RaytheonXie_MSFT

If you check the url i send you there is a top=25 already, and this query is designed no results at all. So i am pretty sure the case is not on thre returns results set but on the item count on the specified DL

Answer 3

Hi @Giorgos Poulis
You could try to perform a repeated ajax call function filtering 5000 items each time. But to know how many times to call the ajax function, we must do in Dynamic way.
First call a ajax function with parameters "/_api/Web/Lists/GetByTitle(ListName)/Items?$orderby=Id desc&$top=1". Now you will get the latest added "Id".
Then divide the Id returned by the ajax by 5000. (Id/5000) So that you will get a result of how many times you need to perform the ajax.
Now you can perform the ajax function repeatedly be filtering every 5000 items, with filter like, $filter=Id ge 0 and Id le 5000, $filter=Id ge 5000 and Id le 10000, ........ , etc.
You can have a foreach loop to perform the ajax repeatedly with a dynamic filter, $filter=Id ge (5000*LoopValue) and Id le (5000*(LoopValue+1)).