# ElasticSearch SQL pagination and format

**URL:** <https://discuss.elastic.co/t/elasticsearch-sql-pagination-and-format/137066>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [June 22, 2018, 6:26pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-pagination-and-format/137066 "2018-06-22T18:26:29Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jurquiza](https://avatars.discourse-cdn.com/v4/letter/j/7feea3/32.png) [@jurquiza](https://discuss.elastic.co/u/jurquiza)\
**Post date:** [June 22, 2018, 6:26pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-pagination-and-format/137066/1 "2018-06-22T18:26:29Z")

</div>

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 😀

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [June 25, 2018, 8:00am UTC](https://discuss.elastic.co/t/elasticsearch-sql-pagination-and-format/137066/2 "2018-06-25T08:00:35Z")

</div>

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

```auto
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](https://www.elastic.co/guide/en/elasticsearch/reference/6.x/search-request-scroll.html) 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.

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [June 25, 2018, 8:22am UTC](https://discuss.elastic.co/t/elasticsearch-sql-pagination-and-format/137066/3 "2018-06-25T08:22:58Z")

</div>

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](https://github.com/elastic/elasticsearch/issues/31549) for this feature to be added.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 23, 2018, 8:24am UTC](https://discuss.elastic.co/t/elasticsearch-sql-pagination-and-format/137066/4 "2018-07-23T08:24:08Z")

</div>

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