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.

[ifooling]

How long?

[hank583746309]

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

[caihaibin1991]

me too.... dear ....

[ghost]

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.

[brunoanasofia]

Hello @astefan. We are in May 2021. Do you still have this issue on your map? I can´t paginate backwards using a cursor. Is there another solution for this problem? Thanks.

[astefan]

@brunoanasofia things are the same as they were in December 2020 (when my latest reply was added).

[ARdreamer]

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

[Robbie-Perry]

@astefan Any updates on this? Seems like it would be something fairly trivial to add, considering elasticsearch querying supports this behaviour with the "from" parameter.

For now, the workaround that I'm doing is as follows:

• Remove OFFSET clause from SQL
• Pass SQL into SQL Translate API
• Manually add "from": n to returned elasticsearch query
• Pass query into Search API

[edu-de]

Hi,
I need also pagination support (with OFFSET or any other similar SQL construct). Is this still planned ?