Joining more indexes and paginate the result

Elastic Stack Elasticsearch
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

We don't support opendistro in this forum. You need to ask in their forums if you are not using the standard distribution.

Note that in elasticsearch we recommend doing "joins" at index time.
If you want to do joins at search time, you should look at parent/child feature. May be that could work. Not sure if it's supported by this project though.

3

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