Join query #

Status: functional (semi-join). Joins need a running node — the gateway orchestrates and the engine executes, which is why the wasm playground does not host them. The joins tutorial walks the complete relational sequence — references → join → FK-routed writes → salting → ?routing= addressing — step by step against a live node.

Matches documents in the outer collection — the one in the request URL — whose field values intersect the join_on values of documents matching a query on the inner collection — the one named by target. The result is a semi-join: outer documents that have at least one matching inner document, each reported once. It is the moral equivalent of SQL’s

SELECT * FROM orders
WHERE org_id IN (SELECT _key FROM orgs WHERE tier = 'gold')

except the two sides live in different collections and the engine runs them in one request.

Naming the target #

target names the inner collection — either a bare string or the explicit object form, mirroring the schema language’s references object:

"target": "orgs"
"target": { "collection": "orgs" }

The object form also carries an optional index key for addressing an index within the collection — reserved vocabulary: the serving layer resolves joins at collection granularity today, and a request that sets index is rejected with a 400 saying so, not silently approximated.

How it executes #

The work splits in two, and neither half fan-outs blindly:

1. Gateway (orchestration) — runs the inner query on the target collection, distributed across its shards, and extracts the join_on values from every hit. These values — the join keys — attach to the join node as a prepared payload.
2. Engine (execution) — each outer shard receives the keys and runs the join as a local membership filter. The gateway never joins data itself, and the outer side never re-queries the inner collection.

graph LR Q["join request
on orders"] --> GW("gateway") GW -->|"run the inner query
on orgs"| K["join keys
org-a, org-mega"] K -->|"keys ride the join node"| E("each orders shard —
local membership filter") E --> H["outer hits, each reported once"]

The outer side routes keys per shard, not per request: when the outer collection routes by _key (no hash-routing field) and field is "_key", the preflight licenses each outer shard to drop the batch keys whose routing partition cannot live on it before resolving them — exact by the routing invariant, never more than skipped impossible lookups.

How the engine resolves the keys depends on field:

A field that declares references without "ref": true still joins correctly — it degrades to the keyword rung, and the gateway logs a warning saying so ("ref": true + reindex promotes it to the column path).

A declared references also pins the join’s shape: the join’s target and join_on: "_key" must match what the field declares, or the request fails with a 400 rather than executing a value join through a field the schema says means something else.

Repeated joins amortize: an identical inner request re-issued by auto-fired patterns is served from a dimension cache validated against exact per-shard visibility tokens (any write, failover or restart invalidates it) — never a TTL, never a stale hit.

Walkthrough: orders of gold-tier orgs #

Two collections — a dimension (orgs) and a fact (orders) whose org_id declares the full relationship (see relational parameters for what each part costs and buys):

PUT /orgs
{
  "schema": {
    "dynamic": "strict",
    "properties": { "tier": { "type": "keyword" } }
  }
}

PUT /orders
{
  "schema": {
    "dynamic": "strict",
    "properties": {
      "status": { "type": "keyword" },
      "org_id": {
        "type": "keyword",
        "immutable": true,
        "references": { "collection": "orgs" },
        "routing": true,
        "ref": true
      }
    }
  }
}

Seed one silver org and two gold ones, then ten orders — three for org-a, one for org-b, six for org-mega:

PUT /orgs/_doc/org-a    { "tier": "gold" }
PUT /orgs/_doc/org-b    { "tier": "silver" }
PUT /orgs/_doc/org-mega { "tier": "gold" }

PUT /orders/_doc/a-1 { "status": "new", "org_id": "org-a" }   # …a-2, a-3
PUT /orders/_doc/b-1 { "status": "new", "org_id": "org-b" }
PUT /orders/_doc/m-1 { "status": "new", "org_id": "org-mega" } # …m-2 … m-6

Now the join — orders (outer, in the URL) whose org matches a query on orgs (inner, named by target):

POST /orders/_search
{
  "size": 10,
  "track_total_hits": true,
  "query": {
    "join": {
      "target": "orgs",
      "query": { "term": { "field": "tier", "value": "gold" } },
      "join_on": "_key",
      "field": "org_id"
    }
  }
}

The inner term matches org-a and org-mega (gold), their _keys become the membership filter, and the answer is 9 orders — the three a-* plus the six m-*. b-1 is absent: its org is silver. Flip the inner value to silver and exactly b-1 comes back.

The inner query is any leaf or bool — a full-text match works the same way:

POST /orders/_search
{
  "query": {
    "join": {
      "target": "orgs",
      "query": { "match": { "field": "name", "query": "infini" } },
      "join_on": "_key",
      "field": "org_id"
    }
  }
}

Joining on values, not keys #

join_on names where the keys come from on the inner side — it does not have to be _key. Any inner field works: its values are extracted from each inner hit (scalars verbatim, arrays expand to one key per element, objects and nulls are skipped) and matched against the outer field:

POST /invoices/_search
{
  "query": {
    "join": {
      "target": "orgs",
      "query": { "term": { "field": "tier", "value": "gold" } },
      "join_on": "code",
      "field": "org_code"
    }
  }
}

orgs.code values (say "A-100") become the key set; invoices matching on their own org_code keyword field survive. This shape cannot ride the _ref column path — the fast column path exists only for joins that follow a declared references (join_on: "_key", field the FK field).

Where a join may sit #

• Inside bool.must, bool.filter and nested — resolved and executed.
• Inside bool.should or bool.must_not — inert: an unresolved join node matches nothing, so a join under should quietly contributes no hits (v1). Put joins in must/filter.
• Inside another join’s inner query — rejected with a 400 (no join-of-joins).

Compose it with ordinary clauses like any filter:

POST /orders/_search
{
  "query": {
    "bool": {
      "filter": [
        { "term": { "field": "status", "value": "new" } },
        { "join": {
            "target": "orgs",
            "query": { "term": { "field": "tier", "value": "gold" } },
            "join_on": "_key",
            "field": "org_id"
        }}
      ]
    }
  }
}

Add "explain": true to the request and the response narrates the join plan — which locality tier it took (colocate or key-directed) and, when a better tier was skipped, why.

Scoring #

score_mode controls how inner-query scores reach the outer document:

• none (default) — pure filter; inner scores are discarded and the outer document keeps its ordinary query score.
• max / sum / avg — combine the scores of the matching inner documents into the outer score.

In v1 only the _key rung carries scores — both field-targeted rungs (_ref column, keyword terms) are filter-shaped and reject score_mode values other than none with a 400.

Guardrails #

• inner_hits_cap (default 10 000) bounds the inner query. When the inner hit set reaches the cap the key set may be truncated — the join result is then a lower bound, never silently complete, and the gateway logs the truncation at preflight. Raise the cap if the dimension side is legitimately larger.
• Namespace sharing — the inner query runs in the outer request’s namespace, so one tenant’s authorization covers both collections.
• Point-in-time — a join under a PIT requires the PIT to cover both collections (same pinned snapshot on each side); a PIT that does not cover the join target is rejected with a 400 instead of silently mixing a pinned outer with a realtime inner.
• Fail-closed errors — 400 for a missing target collection, an index-level target (the index key inside target is reserved vocabulary — rejected until index-level joins exist), a field absent from the outer schema, a non-keyword field, a join contradicting a declared references, a join inside a join, or a scoring rung mismatch; 503 when the target has no started primary shards.

Placement features compose with joins but are declared separately: routing: true colocates an org’s facts on one partition (by-key reads then take ?routing=), and salting splits a hot value across a partition set — salting changes placement, never join visibility. The joins tutorial walks the whole story against a live node, and traverse covers the graph-shaped sibling of this query.

Use cases #

• Dimension-filtered facts. “Orders of gold-tier orgs” without copying tier onto every order: the dimension stays small and editable, the facts stay big, and each request re-derives membership. The teaching customers collection is the same shape at toy scale — a 20-document dimension split gold 4 / silver 8 / bronze 8 that a fact collection could join against.
• Business-key scoping. Facts that carry a natural code (org_code, sku) rather than a foreign key: join_on: "code" matches values, not keys, so reference data keyed by its own code joins without a reindex — the keyword rung, no _ref fast path.
• Continuously re-fired filters. Dashboards that auto-refresh the same inner query are exactly what the dimension cache amortizes: one validated version read per shard instead of a distributed inner search, invalidated by any write, failover or restart.
• Placement-composed reads. Declare routing: true on the FK and one org’s facts colocate on a partition, so by-key reads take ?routing= after the join narrows the candidates — placement composes with join visibility.

Parameters for join #

• target
  (Required, string or object) The inner (foreign) side to query: a bare collection name, or {"collection": ...} with an optional reserved index (rejected today — see Naming the target).
• query
  (Required, query) Query to execute on the inner collection. Any leaf or bool composes here — except another join.
• join_on
  (Required, string) Field in the inner collection whose values become the join keys. Use "_key" to join on the inner document primary key; any field name extracts that field’s values (arrays expand, scalars verbatim).
• field
  (Required, string) Field in the outer (current) collection matched against the inner join keys. "_key" matches the outer primary key; a declared keyword field executes engine-side; a references field with "ref": true rides the fast _ref column path.
• score_mode
  (Optional, string) How inner-query scores affect the outer document score: none (default — pure filter, inner scores are discarded), max, sum or avg. Field-targeted paths accept none only in v1.
• inner_hits_cap
  (Optional, integer, default: 10000) Maximum number of inner hits to collect — protects against runaway inner queries. A join that hits the cap is a lower bound and is logged.

Related #

• nested — the intra-document counterpart: key membership across collections vs same-element correlation inside one document.
• traverse and graph — the graph-shaped siblings over a relation edge field within one collection.
• Relational parameters — references, ref and routing: what the schema side of a join costs and buys.
• Nested aggregation and children aggregation — bucket-side analytics over nested and parent/child structures.