Elasticsearch SQL join across indices

Why Elasticsearch has no server-side join#

An Elasticsearch index is a set of JSON documents spread across shards. A search request is routed to the shards of one index or one index pattern, each shard answers for the documents it holds, and the coordinating node merges the answers. Nothing in that flow can look up a document in a different index while scoring or filtering, so there is no JOIN in Query DSL. The cluster's own SQL inherits the restriction: the x-pack SQL SELECT reference states that FROM supports exactly one table. Elastic offers two in-cluster workarounds, the join field for parent and child documents and the enrich processor, and both move the relationship into the index at write time. They are covered below.

The result is a familiar situation: organizations and suppliers sit in the same cluster, share a domain, and still cannot be queried together without code.

How FlatQL joins two indices#

FlatQL parses your statement, fetches each index named in FROM or JOIN, loads the rows into an embedded DuckDB engine and runs the join there. The interesting part is what reaches the cluster:

1. Filters are pushed per side. A WHERE conjunct that mentions only one table and compares a field with a literal becomes a term, terms or range filter on that side. The engine's rule, quoted from the source: "a conjunct is pushed only when it maps 1:1 to a filter (column vs literal on a field we can filter exactly). The full WHERE still runs in DuckDB; pushing just shrinks the fetch."
2. The smaller side goes first. Tables with a LIMIT are fetched before tables with filters, and tables with filters before tables with neither.
3. Join keys travel to the other side. Once the first side is in memory, its distinct join-key values are sent to the second side as a terms filter (an ids query when the key is _id). The second index therefore only returns documents that can match.
4. DuckDB joins. Inner, left, right, full and cross joins, USING, and ASOF JOIN all run in the engine, together with any ORDER BY, LIMIT and expressions the cluster could not take.

Every result carries a note per index, for example "suppliers was filtered to the 1,204 join keys found in organizations", and Show as DSL prints the exact request each side received.

Example: organizations joined to suppliers#

British organisations founded since 2015, with any supplier that uses the same domain and has a rating of four or better.

SELECT o.name, o.country, s.name AS supplier, s.category, s.rating
FROM organizations o
JOIN suppliers s ON s.domain = o.primary_domain
WHERE o.country = 'GBR'
  AND o.founded_year >= 2015
  AND s.rating >= 4
ORDER BY s.rating DESC
LIMIT 100;

Both sides have filters and neither has a table-level limit, so organizations is fetched first in FROM order. The request is a POST /organizations/_search?scroll=2m, scrolled in pages of 5,000 and split into parallel slices. Only the columns the statement uses are requested:

{
  "query": {
    "bool": {
      "filter": [
        { "term": { "country": "GBR" } },
        { "range": { "founded_year": { "gte": 2015 } } }
      ]
    }
  },
  "_source": ["country", "name", "primary_domain"],
  "size": 5000,
  "sort": ["_doc"],
  "track_total_hits": true
}

The distinct primary_domain values that came back are then added to the suppliers side. The second request is a POST /suppliers/_search?scroll=2m with the rating filter and the join keys together (the list is shortened here):

{
  "query": {
    "bool": {
      "filter": [
        { "range": { "rating": { "gte": 4 } } },
        { "terms": { "domain": ["acme.co.uk", "bramble.io", "northwind.org"] } }
      ]
    }
  },
  "_source": ["category", "domain", "name", "rating"],
  "size": 5000,
  "sort": ["_doc"],
  "track_total_hits": true
}

ORDER BY s.rating DESC and LIMIT 100 are not in either request. The engine's rule is explicit: "LIMIT / ORDER BY: pushed only for single-table statements with no aggregation, DISTINCT, window or QUALIFY, and only when the whole WHERE was pushed." A join is never single-table, so the sort and the limit apply after the join, which is the only place they can be correct.

If you join on a text field that has a .keyword sub-field, the terms filter uses domain.keyword automatically. Write s.domain in SQL, never the sub-field; FlatQL picks it. More on field choice in type mapping.

Without FlatQL#

Application-side join. Run one search for the organisations, collect the domains, run a second search for suppliers with a terms query on those domains, then merge in code with a dictionary keyed on domain. This is exactly the plan FlatQL executes; the difference is that you write and maintain it per question, and ad-hoc analysis in a notebook becomes a small program each time.

Denormalise at index time. Copy the supplier fields you need into each organisation document when you index it. Queries become single-index and fast. The cost is write-side: a supplier's rating changes and every organisation that references it needs a reindex, and the question "which supplier fields will we ever need" has to be answered up front.

Enrich processor. An ingest pipeline with an enrich processor looks up a value in a source index and adds matching fields while a document is being indexed. It is the managed form of denormalising: the lookup table is maintained as an enrich policy that you re-execute when the source changes, and documents indexed before the policy existed are not enriched until you reindex them.

Parent/child with the join field. Organisations and suppliers can live in one index as parent and child documents, which lets has_child and has_parent queries relate them at search time. Both sides must share a shard, so routing is fixed, and the result is still a query that returns one side's documents, not a flat row with columns from both.

Each approach is right for something: denormalising suits a read-heavy production path, the enrich processor suits a stable lookup table, parent/child suits a true hierarchy. FlatQL is for the case where the question arrives after the data is indexed.

Limits you should know#

• Fetch cap. The engine reads at most about 50,000 rows per index per statement. When an index has more matching documents than that, the response says so ("only the first 50,000 were scanned") and asks for filters Elasticsearch can apply or a LIMIT. Pushed filters are the lever: o.country = 'GBR' turns a scan of the whole index into a scan of one country.
• Join-key cap. Up to 50,000 distinct keys are sent to the other side. Above that, or when the first side was truncated, the second side is fetched with its own filters only and the join still runs, but the sample may be incomplete and the result carries a warning.
• Qualify your columns. In a multi-table statement, an unqualified column cannot be attributed to a table, so FlatQL requests every field from every side. Write o.name and s.name.
• Keys must be filterable fields. keyword, numeric, date, boolean and ip fields, or a text field with a .keyword sub-field. A plain analysed text field cannot carry a terms filter, so the second side would be scanned.

The joins reference lists the join types, and pushdown explains what travels to the cluster for every clause. For how this compares with the cluster's own SQL on either platform, read FlatQL vs Elasticsearch SQL and FlatQL vs OpenSearch SQL; the OpenSearch SQL plugin does offer a two-index join with restrictions, which that page covers.

Try a join on your cluster#

Joins are part of the Free plan, which covers one cluster with the full engine and Show as DSL; the pricing page lists what Pro adds. Create an account, connect a cluster with your own credentials and run the statement above against your own indices.