It's because Elasticsearch is not a relational database. So what you can find obvious in relational database might be a bit trickier in a Document oriented search engine.
I think that the coming ES|QL engine might be able to solve such use cases but I did not test it yet.
I believe that for now you would need to run 2 queries separately.
The problem is; I need the result to be used as source of a Kibana visualization, can Kibana run two queries and agrregate the result based on a field ?
It gives me some ideas how we can do that, however one problem here is I have huge indices (~ 200 Go daily ) reindexing this can be very slow on this infra.
So I think another easy way is to add the filed of the join on the docs, this way we can filter directly in the index.
I post another question on the Kibana forum, if somone have an idea how to do it via Kibana.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.