Join-like query for multiple indexes

Question

I have 2 entities stored in separate indexes:

• City index has 2 fields mapping: name:keyword and url:text.
• Product index has 2 fields mapping: name:keyword and city:text

I would like to query all Products by City's url.

Example:

Given: Search all shirts by url "http://shirts-shop.com/frankfurt"

Then (step 1): Search all cities where url is "http://shirts-shop.com/frankfurt" — it will return "Frankfurt" city

Then (step 2): Search all shirts by city "Frankfurt"

In SQL databases it is quite simple to write: we just need to use 'join' query. How to write such query in ElasticSearch 6.5 ?

WARN: Entities are in separate indexes, because as said in documentation ElasticSearch starting from version 6 recommends to use 1 index per mapping.

Answer 1

As per my understanding the url gives the name of city.

i.e. http://shirts-shop.com/<_city_>

From this we can extract city name In the index Product I would suggest to keep the data-type of city as keyword instead of text (so that it doesn't get analyzed).

To get shirts in <_city_> use the term query:

{
  "bool": {
    "must": [
      {
        "terms": {
          "city": <_city_>
        }
      }
    ]
  }
}