Joining more indexes and paginate the result

Plugins SQL
1

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
2

Hi @m.depascale, we do support JOINs with 2 indices involved. For now we only support pagination for simple query (no GROUP BY and no JOINs): Add Simple Cursor Support · Issue #16 · opendistro-for-elasticsearch/sql · GitHub. As for search_after, our pagination is using ES Scroll.