Elasticsearch: Consider adding support for OFFSET

[astefan]

At the moment, ES SQL supports LIMIT only. Would be helpful to also support OFFSET to allow users to use pagination.

[elasticmachine]

Pinging @elastic/es-search-aggs

[chenrenfei]

good，please add return total record count for pagination！

[flydocs-jignesh]

Any hope?

[astefan]

@flydocs-jignesh As an update, OFFSET is not on our near future feature list.

[ithinksing]

How long?

[hank583746309]

I want know how long . can support 'offset' too. dear~

[caihaibin1991]

me too.... dear ....

[thomasj-git]

Hope to support offset as soon as possible!!!!

[caihaibin1991]

I use /_xpack/sql/translate to turn the sql to be es sql, and then append from to strip the offset ....

[leanderli]

It's really useful.Hope to support it ...

[wdgrantham]

Just hopping on here. I've been migrating a very large MySQL database over to elastic search to improve search speed. A key part of my application is allowing my users to pull lots of data from my database, and I've historically used offsets to ensure that they don't get duplicate records.

Now that I know that elastic search sql doesn't support offsets, I'm having to write some funky logic to do it manually. For example, where I used to use a simple offset command like this:

SELECT fn, ln, addr from "Db"."Table" WHERE (fn like 'And%') LIMIT 5000 OFFSET 5000

I'm now having to write something like this:

SELECT id, fn, ln, addr from "Db"."Table" WHERE (fn like 'Jam%') and (id > ${last_id_user_downloaded} ) ORDER BY id LIMIT 5000

Then I have to strip the id field from the results of all the rows before I create the download.

Would really appreciate if you all could support OFFSET

[chenrenfei]

Why not consider pagination by sql？
and provide an example？

[Mokaful]

I'm very very very eager for pagination.when can we get it?

[leanderli]

I'm very very very eager for pagination.when can we get it?

Opendistro-for-elasticsearch SQL support it and powerful.

[sephiroka]

Does new version's ES-SQL provide solutions to help paging?

[cloder]

Is there any news about this?

[astefan]

OFFSET is not on our map at the moment.
Even though this is not the same, pagination is possible in ES SQL _in a performant way_ through the use of cursors: https://www.elastic.co/guide/en/elasticsearch/reference/master/sql-pagination.html.