Sharepoint Online REST API: list view threshold exceeded when querying items in subfolder with GetItems

Question

I need to query items in a given folder and all of its children in a document library with as few APIs as possible. The ideal solution seemed to be getItems with Scope='RecursiveAll' and a FolderServerRelativePath. However the API below returns an SPQueryThrottledException for a specific document library on Sharepoint Online, and I can't figure out the reason.

POST <web-url>/_api/Web/Lists(<list-id>)/getItems?$select=*,FileRef

POST BODY:
{
    "query": {
        "odata.type": "SP.CamlQuery",
        "ViewXml": "<View Scope='RecursiveAll'><RowLimit>1</RowLimit></View>",
        "DatesInUtc": true,
        "FolderServerRelativePath": {
            "DecodedUrl": "<subfolder's-server-relative-path>"
        }
    }
}

Some observations:

• The subfolder has 4 files and no folders in it.
• Getting items under any folder in the document library fails with the same error
• The document library has around 6.5k items (folders+files) in it
• Scope='Recursive' also fails, but 'Default' and 'FilesOnly' work fine
• The same query worked for another document library with 100k files

What's the problem with the query? How can I fix it? If it's impossible to fix, are there any other APIs that can recursively query items in a folder?

Answer 1

Replace the "FolderServerRelativePath" with "FolderServerRelativeUrl". The following code for your reference.

<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function(){
    var viewXml = "<View Scope='RecursiveAll'><RowLimit>1</RowLimit></View>";
    getListItems(_spPageContextInfo.webAbsoluteUrl,'DL0506',viewXml,'/sites/lz/DL0506/folder1').then(function(data){
         var items = data.d.results;
         for(var i = 0; i < items.length;i++) {
             console.log(items[i].FileRef);
         }    
    }).fail(function(error){
        console.log(JSON.stringify(error));
    });
});
function getListItems(webUrl,listTitle,viewXml,folderUrl){    
    var url = webUrl + "/_api/web/lists/getbytitle('" + listTitle + "')/getitems?$select=*,FileRef"; 
    var queryPayload = {  
               'query' : {
                      '__metadata': { 'type': 'SP.CamlQuery' }, 
                      'ViewXml' : viewXml,
                      "FolderServerRelativeUrl": folderUrl 
               }
    };
    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"
           }
     });
}
</script>

If you want to get all the files along with associated list items from a specific folder, we can using the REST API below to achieve it.

/_api/web/getfolderbyserverrelativeurl('<serverrelativefolderurl>')/files?$expand=ListItemAllFields