In Elasticsearch it's common to denormalize data, basically making one big flat "table" , then you can write the above as a simple query/aggregation.
Elastic is not a relational detabase, and the data model you' re describing is something that works in an SQL database, but not so much in elastic.
There is some (limited) possibilites for relations in elastic (parent/child , nested docs , terms lookup) but i think denormalizing is probably most suitable to what you describe above.