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.

I guess you can solve your problem referring to this previously asked question: https://stackoverflow.com/questions/24315753/elasticsearch-querying-multiple-indexes-is-possible — nima, Nov 18 '18 at 15:11
You are on the right way but I know that it is possible. I don't know how to write such query. Also, imagine if entities have fields with same name: what to do then? I am a bit newbie in ES. — Meiblorn, Nov 18 '18 at 15:14
You can specify your desired fields inside your query by referring to index by dot. like this `city.name` or `product.name`. Your query must be something like this: `{"query": {"bool": [{"must": [{"match": {"city.name": "frankfurt"}}, {"match": {"product.name": "whatever" } } ] } ] } }` — nima, Nov 18 '18 at 17:23
I see only 1 single solution for this: use external service which will call appropriate requests — Meiblorn, Nov 18 '18 at 18:21

score 0 · Answer 1 · answered Nov 19 '18 at 10:48

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_>
        }
      }
    ]
  }
}

Join-like query for multiple indexes

1 Answers1