Backward pagination with search_after when sorting value is null

Elastic Stack Elasticsearch
1

Hello,

I have an application which has a dashboard, basically a table with hundreds of thousands of records.

This table has up to 50 different columns. These columns have different types in mapping: keyword, text, boolean, integer.

As records in the table might have the same values, I use sorting as an array of 2 attributes:

  1. First attribute is what client wants to sort by. It can be a simple sorting object or some sort query with nested filter.
  2. Second attribute is basically a default sorting by id, needed for sorting the documents which have identical values for the column customer wants to sort by.

I checked multiple topics/issues on github and here on elastic forum to understand how to implement search_after mechanism for back sorting but it's not working for all the cases I need.

Please have a look at the image:

image
image1396×212 17.1 KB

Imagine there is a limit = 3, the customer right now is on the 3d page of a table and all the data is sorted by name asc, _id asc

The names are: A, B, C, D, E on the image.
The ids are numeric parts of the Doc word.

When customer wants to go back to the previous page, which is a page #2 on my picture, what I do is pass the following to elastic:

sort: [
    {
      name: 'desc'
    },
    {
        _id: 'desc'
    }
],
search_after: [null, Doc7._id]

As as result, I get only one document, which is Doc6: null on my image. It seems to be logical, because I ask elastic to search by desc after null and id 7 and I have only 1 doc corresponding this..it's Doc6 but it's not what I need.

I can't make up the solution to get the data that I need.

Could anyone help, please?

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.