Joining more indexes and paginate the result

Describe the feature:
I’d like to be able to join more indexes and paginate the result

Elasticsearch version: 6.8.0

URL: POST … …/_opendistro/_sql/

Actual query:
"query": "SELECT * FROM file f JOIN document d ON d.id = f.documentId WHERE d.documentCategoryId = '..." AND d.state = '...' LIMIT 5 "
this query is working fine, but it can skip the first x results

Feature request
"query": "SELECT * FROM file f JOIN document d ON d.id = f.documentId WHERE d.documentCategoryId = '..." AND d.state = '...' LIMIT 5 OFFSET 5"

I don’t actually need _opendistro/_sql/ if you have other options like:

  • can I translate SQL query with JOIN in ES query?
  • any way I can join 2 indexes and use size and from
  • is there any option with search_after or from