Two indices documents as below:
class First(Document):
class Index:
name = 'first'
case_id = Keyword()
name = Text()
class Second(Document):
class Index:
name = 'second'
case_id = Keyword()
status = Text()
I just want to execute a query like below in SQL format
select * from first as f, second as s where s.case_id = f.case_id or s.status = 'xyz'
How can I do it using elastic search dsl query?
Elastic Search doesn't support joins between indexes
Reason: Elastic Search is not relational and denormalized data should be stored here.
Excerpt from elastic Doc below :
Performing full SQL-style joins in a distributed system like Elasticsearch is prohibitively expensive. Instead, Elasticsearch offers two forms of join which are designed to scale horizontally.
Use either nesting or parent/child mapping to store your data, based on the use case.
Nesting: If cardinality of nested doc is very low and indexes are read intensive
Parent/child: If cardinality of child is very high than parent and parent/child needs to be updated frequently
