Search in two indexes

[TomTom]

I have two indices.

1. Customer: List of all customers, their internal identifier (id) and their name (name);
2. Order: List of all purchases made, with purchase date (order_date), customer id (customer.id) and name (customer.name).

Is it possible with Elasticsearch to search for all customers in the Customer index that do not have purchases in the Order index?

If a customer has never made a purchase they will never have data in the Order index, but it should still appear in search results.

[warkolm (Mark Walkom)]

This is a join, so no.

You can query for each unique customer ID/name that exists in the order index. You'd then need to compare that to the customer index to see what's missing.

[Tomo_M (Tomohiro Mitani)]

This transform approach could be another option, while there are some tradeoffs.

How to join two index

I like to add another option: transform. A transform in a nutshell is a task that runs aggregation queries and persists the result in an index. In order to join 2 indices you need compatible mappings for grouping, e.g. a key that is named the same way and has the same type. Note, this can be achieved with scripts or runtime fields, however if you want to run in it continuously at scale, it is advised to use proper ordinary mappings. For the group_by use terms on the common key. For the aggreg…