Today I learned that we can do joins in Elasticsearch

Today I learned that we can do joins in Elasticsearch called lookup joins 🤯

In Elasticsearch, ES|QL has LOOKUP JOIN, which allows us to join different indices. It essentially performs a left outer join operation and enriches the primary dataset with matching records from a lookup table (another smaller index). Here’s an example

FROM logs-000001
| LOOKUP JOIN users ON user_id
| WHERE department == "engineering"
| STATS count(*) BY team

The field user_id is a field in both logs-* and users indexes, and the users index also contains attributes like department and team.

Internally, lookup tables (indexes) are loaded into memory on the coordinating nodes; hence, we should keep them reasonably sized (typically under 100MB) and they should reside on a single local shard.

Also, this query will circuit break if there are too many matching documents in the lookup index, or if the documents are too large.

Hope you found this interesting.

By the way, this came up today during my sys design cohort session, where we were discussing information retrieval.

And, this is precisely why I love my cohort-based setup. where it is never a one-way information flow, but every single cohort participant shares their experiences and we all learn a thing or two :)