How to use pagination in Elastic Search SQL?

[KrishK (K P)]

Hi

We are using elastic search SQL REST API.

POST /_sql?format=json
{
"query": "SELECT * FROM library limit 100"
}
If i use limit 0,100, its not working.
And, i want to use pagination in application. is there any way to use offset in query?
Any other way to achieve pagination functionality.

Thanks

[Andrei_Stefan]

@KrishK Offset is not supported, but there is an issue created for that: https://github.com/elastic/elasticsearch/issues/31549.

Regarding "pagination", in SQL world there is a similar approach using cursors. Behind the scene, for ES SQL, this is implemented using the scroll API. More about cursors here: https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-pagination.html

[KrishK (K P)]

Thanks @Andrei_Stefan,

If we use cursor/Scroll API, is that possible to get results from the previous page?.

Thanks

[Andrei_Stefan]

@KrishK no, it is not possible to go back.

[KrishK (K P)]

Thanks @Andrei_Stefan.