filter data for more than 5k items through pnpjs SPFx

Question

Here is the situation :

1. I have a list with 50k items.
2. I have to get data from that list with a filter on status column (Status = true).
3. So basically there are items with status true/false.
4. When I will filter (Status = true) the items returned are 18k.

How will I filter data for above condition through PnP JS SPFx.

 await sp.web.lists
        .getByTitle("listName")
        .items.select("ID", "Status", "Title")
        .filter("Status eq 'True'").get();

This is not working.

Answer 1

This is known limitation of SP PnP JS / SharePoint REST APIs.

You cannot add filter on SharePoint list items where returned items/results will be more than 5000.

You have to get all SharePoint list items first & then apply client side filtering using JavaScript.

Answer 2

Quote from the official documentation:

Using the items collection's getAll method you can get all of the items in a list regardless of the size of the list:

Example

const allItems: any[] = await sp.web.lists.getByTitle("BigList").items.select("Title").filter("Title eq 'Test'").getAll();

In your case, this is the code that should work:

await sp.web.lists
        .getByTitle("listName")
        .items.select("ID", "Status", "Title")
        .filter("Status eq 'True'").getAll();

If this code does not work, make sure your list has indexes for the Status and Title columns.

Answer 3

What the official documentation doesn't say, it's limited to 2000 items by default. You can check the information of the getAll() method.

Answer 4

To avoid view threshold, use filter by indexed field (no more than 20 indexes in list). More info here.

Also, you can use Search. It doesn't care about list view threshold at all.