ElasticSearch SQL pagination and format

[jurquiza (Juan Ignacio Urquiza)]

Hello!

  Im currently developing a web api and im using elastic as a GET method(with REST). 

I´ve seen that every time you perform a search a new cursor is created, if i try to go back to a previous cursor(lets say from page 3 to 2 for example), it doesnt go back to the previous state, it just keeps going forward (for example page "3" is expected to have id´s from 20 to 30, so i set a cursor query with page 2's cursor and the results i get a id from 40 to 50)
Is this a bug? Is there a way to go to previous states of searches (like size & from aproaches)?
Also, is there a way to get the response format of a normal search?
Thanks in advace

[Andrei_Stefan]

Hey there!
I'm assuming you are referring to this type of query:

POST /_xpack/sql?format=json
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 5
}

where you specify a fetch_size. In the background this uses the scroll API of Elasticsearch which is not a true form of pagination, but more a tool to get back a lot of documents. In the traditional way of retrieving many many documents, ES will use too much memory and it's impractical to do it, but with scrolling you get back lots of them without the performance penalty.
You cannot go "back" in a scroll, always move forward with it.

[Andrei_Stefan]

On a related note, at the moment Elasticsearch SQL supports LIMIT, but not OFFSET. This combination would be closer to the from and size combination in Elasticsearch and would help with pagination. I've created this GH issue for this feature to be added.