I have a list which has more than 5K items in it and I need to filter it by using multiple columns. Below is the challenge that I am facing.
Scenario 1: Below REST API fails with Microsoft.SharePoint.SPQueryThrottledException
because Group
column has more than 5K items with value as Group5K
.
/_api/web/lists/getbytitle('List5K')/items?$filter=Group eq 'Group5K'
Scenario 2: Below REST API works as SubGroup
column has less than 5K items with value as SubGroup1
/_api/web/lists/getbytitle('List5K')/items?$filter=SubGroup eq 'SubGroup1'
Scenario 3: Below REST API works as combination of filter conditions from scenario 1 and 2 have less than 5K items.
/_api/web/lists/getbytitle('List5K')/items?$filter=SubGroup eq 'SubGroup1' and Group eq 'Group5K'
Scenario 4: If I change the sequence of filter conditions from scenario 3, below REST API fails with with Microsoft.SharePoint.SPQueryThrottledException
/_api/web/lists/getbytitle('List5K')/items?$filter=Group eq 'Group5K' and SubGroup eq 'SubGroup1'
It seems to me, in scenario 4, SharePoint is applying the first filter condition and then on the result it applies the second condition. Is this default behavior of SharePoint? Is there any workaround for this?
I may have a case where SubGroup
column condition may also start returning more than 5K items but the combination of Group
and SubGroup
column will return less than 5K items. In that scenario changing the sequence will also fail.
How can I query large list with multiple columns?
P.S. Group
and SubGroup
columns are indexed and the list is on SharePoint Online.