Correct way to perform pagination on aggregation results

OpenSearch
1

Apologies in advance for the rant, but we are trying to migrate from Elasticsearch 7.17 to latest opensearch latest version 2.8.0 or 2.11.0 and seeing lot of painpoints

Versions (relevant - OpenSearch/Dashboard/Server OS/Browser): 2.8.0

Describe the issue:
What is best way to paginate aggregated query results? AFAIK json aggregation does not support pagination.
SQL does support pagination but it seems broken and adding fetch_size parameter seems to cause some issues
If I remove fetch_size parameter when using SQL API I can paginate using LIMIT 0,10 apporach.

is this correct way to paginate aggregated results?

is cursor based approach supported?

does this approach also works for PPL?

another painpoint is support for runtime/scripted fields in SQL , elastic supports this and makes it very easy to write SQL by adding complex transforms as runtimefields.

Any suggestions/guidance are welcome. Thank you!

2

Hey @sudssf2014

Since you have ES 7.17 only thing i can think of is this documentation here
What I did a while back is stop at ES version 7.10, did a snapshot and moved it over to a new Opensearch 1.3 node but I havent done a ES version 7.17.

Hope that helps

3

Thank you for reply @Gsmitt , data migration is not the issue, I cameup with patching script which makes ES snapshots compatible with Opensearch

my issues are with running SQL and paginating aggregation query results. we have indices with millions of records and after aggregation we get ~100k records which requires pagination

4

Oh nice

Understood, Not sure Ill be any help with that, perhaps someone else may know more.