Elasticsearch: Querying across multiple Indices with relations

Question

Is it possible to query across multiple Indices with regard to relations? I know I can search across multiple Indices, but then I get two disjoint result sets.

For example I have two indices "customer" and "address" that are related by an address-key in the customer index. I want to get all customers named "John" that live in "New York" in one query. Can ES "join" the two result sets from address and customer and give me all Johns from NY?

I get the Feeling ES isn't made for that rdbms-style joining of Indices. Unfortunately flattening the indices into one index isn't an Option. Querying multiple times is also not an option.

Answer 1

ES is document based No-SQL, so you will not really be able to do joins natively, it is not relational: that is the whole point.

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.

https://www.elastic.co/guide/en/elasticsearch/reference/current/joining-queries.html

An efficient solution is to duplicate data, also called denormalizing data, this avoids doing joins but will be heavier in terms of storage and code logic.

The way to get the best search performance out of Elasticsearch is to use it as it is intended, by denormalizing your data at index time. Having redundant copies of data in each document that requires access to it removes the need for joins.

Read: https://www.elastic.co/guide/en/elasticsearch/guide/current/denormalization.html

If you want optimal search speed rebuild denormalized indexes from the existing ones.