ES|QL Joins Are Here! Yes, Joins!

Elasticsearch 8.18 includes our first SQL-style JOIN: ES|QL’s LOOKUP JOIN command, which is now available in Tech Preview and enables data correlation and enrichment with easily updatable lookup datasets. Need to add host and asset info to your events? No problem. Want to check which IP addresses or URLs are on threat intelligence lists? Sure! Make updates to the lookup dataset and use them immediately? Go for it!

Lookup Join is a SQL-style LEFT OUTER JOIN that relies on a new index mode called lookup for the right side. The lookup index could be assets, threat intel data like known-bad IPs, order info, employee or customer info—there are infinite possibilities.

Historically Elasticsearch lacked join capability, although over time a few attempts were made in that direction, like nested and _parent and join field types, and enrich. We typically encouraged the denormalization of data by putting the reference data alongside the events in the same index, or client side joins. These solutions hamper scalability for larger data volumes and can be limiting when there's a high variety of data and use cases. It’s time for Elasticsearch to have joins. And now it’s possible: ES|QL isn’t just a language—it’s built on top of a new compute engine. The ES|QL engine makes it possible to do advanced features like join.

To enable Lookup Joins, we created a new index mode: lookup. A lookup index is mostly like a regular index (mode:standard is the default), which means it’s directly updateable. The main difference is a lookup index is only one shard. Thus, it is limited to 2 billion documents, a Lucene limit for one shard. That should be more than enough to hold the ‘right side’ data for quite a few of the many lookup join use cases. Imposing this limit helps us avoid some of the problems associated with fully distributed joins by reducing the source cardinality of the right side of the join. This also tells us which datasets you’re planning to use as lookup references so we can find ways to optimise them in the future.

Other than the lookup index mode, there are no restrictions on the source data nor any data preparation required to perform a join. That means it's easy to add lookup datasets, and it’s easy to keep them up-to-date.

Some of this has already been possible with the ENRICH command in ES|QL. ENRICH was optimised for ingest time lookups in ingest pipelines, and small datasets that don’t change often, so it was convenient but not ideal. Lookup Join is easier to set up and manage than enrich:

No enrich policies to create
No policy execution
Lookup indices are writeable
There are fewer copies of the data
Better handling of multiple matches: whereas enrich creates multi-valued fields when there are multiple matches, lookup join creates multiple rows. This makes it easier to do further analytics like grouping and aggregating, such as bytes transferred per user or order totals.
Lookup Join allows on-the-fly specification of the match field

Here are just a few of the ways you might use a LOOKUP JOIN:

Join security events with threat intelligence to further enrich an event
Lookup my current set of hosts vs latest threat intel or internal metadata
Correlate IOT readings with static or dynamic metadata (manufacturer, unit of volume)
Join security events with host information for more context and filtering
Add some facts like asset criticality (this user is an executive)
Add in CrowdStrike AID's to a source that has hostnames
Check if certain ip addresses are in threat intel feeds
Add employee information to events

…

The possibilities are endless.

Example usage

Let’s look at some real examples to see how easy and powerful this can be.

As an SRE, you might need to:

Add an environment to logs based on ip address or hostname
Join employee info to logs
Find out which team owns a server

Before LOOKUP JOIN, you’d have to:

Put your reference data (environments) in an index
Define an enrich policy based on that index, specifying policy type, match field, and enrich fields
Execute the enrich policy once to build the enrich index
Repeat for each reference dataset (employees, teams)
Execute the enrich policy whenever the data changes

With LOOKUP JOIN, just put the reference data into a LOOKUP index and off you go!

Imagine you got an alert that there are users getting errors on your ecommerce site. You find web logs where the response is not the 200 HTTP response code for success. You’d like to break these down by environment to see if Production customers are affected, but the logs don’t have an environment field. That’s okay - just add a lookup join on environment, perhaps using a file like this (example CSV file for environments lookup):

Now add the LOOKUP JOIN on the environments lookup index (mine is called envs_lkp):

There are errors in Production and Quality Assurance (QA). We can join against a list of which team owns each web server to see who we should contact.

Below is an example CSV file for teams lookup:

Security analysts love their joins! Maybe they’ll want to:

Identify client IPs that are requesting known bad URLs
Correlate with asset information to group events and assign priority or risk

You could download a list of malicious URLs from URLhaus at Abuse.ch, and upload it to a lookup index.

How to create lookups:

You’ll need at least one lookup index to use lookup join. We’re designing ways to make it easy and convenient to create and update lookups in Kibana. For now, you can start by creating a lookup index in a few ways—the key detail is that the index mode must be “lookup”.

Create lookup index using Index Management:

In the left navigation pane of Kibana, click Stack Management, and then Index Management. On the right side, click Create index.

Provide an index name, and select “Lookup” from the Index mode dropdown:

Click Create to make an empty lookup index.

Create lookup index via APIs:

Create a lookup index using the create index API - just be sure to include the index mode:

Create lookup index using the ML File uploader:

In the left navigation pane of Kibana, click Machine learning, and then File data visualizer (or type “upload” in Kibana’s search bar).

Select or drag and drop your file - CSV works well for this.

After you click Import, provide a name for the index, and click Advanced to reveal Index settings. Here, you’ll want to add the index.mode: lookup.

Complete the File upload by clicking Import. An index and a data view will be created.

In all cases, you can now reference the lookup index in your ES|QL queries.

Addressing common questions

Q: When should I NOT use joins?

Lookup Join is really powerful, but with great power… well, you know. Joins can be an expensive operation, and every join will add some latency to your query. There may still be some scenarios where a join is overkill or not the best option. For example, if there’s a field that you’re very frequently joining into many queries, consider denormalizing it into the left index. Add it at ingest time using an enrich ingest processor, file shipper config, use a Logstash filter, etc. This one-time ingest processing is a trade-off of a slight increase in storage usage for faster queries. This would be most applicable for reference values that don’t change frequently (the username of a user ID) or never change (the manufacturer of a host).

Q: Can I query a lookup directly?

Yes! A lookup index is mostly “just an index”, so you can query it with ES|QL.

FROM <lookup_index> | …

Or by creating a data view.

Q: Can I send data from Logstash or agent?

Yes! Just keep in mind that a lookup index is not a data stream and it’s just one shard, so you can “only” send up to 2 billion documents to each lookup index.

What’s next?

We’re working on removing some known limitations so that lookup join can become generally available. Plus, we’re designing a first-class editing experience for the lookup datasets. Imagine being able to create and edit lookup indices directly in Kibana Discover, or drop a CSV file onto Discover to populate the index. Here’s a mockup of what that might look like:

Early mockup of potential future lookup editing user experience

And lookup joins are just the beginning. We’d like to offer other useful types of joins, like INNER joins or subqueries, and also allow joining against any index (not just lookup indices).

Conclusion

So that's lookup join, available in Elasticsearch 8.18/9.0 as Technical Preview.

Ready to get started? Elastic 8.18 and 9.0 are now available on Elastic Cloud — the hosted Elasticsearch service that includes all of the new features in this latest release. On behalf of the ES|QL team, we're looking forward to your feedback—you can use the Submit feedback button in the ES|QL editor in Discover. And hey, we got all the way through this joins blog without making any joins puns... thanks for joining!

Elasticsearch is packed with new features to help you build the best search solutions for your use case. Dive into our sample notebooks to learn more, start a free cloud trial, or try Elastic on your local machine now.

Report an issue