Lesson 8 — Joins #

Traversal (Lesson 5) walks edges inside ONE collection. A join spans TWO: it answers questions a single collection cannot — orders whose customer is gold-tier, where the tier lives in a different collection.

Joins need a running node (the gateway orchestrates the two sides, which is why the wasm playground does not host them), so this lesson is curl against your Lesson 1 node — no live widget. Like traverse, join stays DSL-side outside the SQL M1 subset.

The shape to keep in mind is a semi-join — the SQL intuition is:

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

executed as one request, with both sides living in different collections.

The use case #

Facts and dimensions live in different collections because they change differently — orders stream in continuously, org tiers trickle. The question spans both. The questions this lesson answers:

“Orders whose org is gold-tier” — the semi-join, Part 1’s walkthrough.
“Documents visible only to members of group X” — entitlement filtering: the same shape, the group set lives in another collection.
“Can I trust the join to mean what the schema says?” — declared references make contradicting joins fail closed (Part 2).
“One tenant dwarfs the others — where do their writes land?” — FK routing and salting (Part 2).

Part 1 — the undeclared join #

Steps 1–3 build the two collections and run the join with nothing declared — two keyword fields are all a join requires. The sections that follow show what actually executed, where a join may sit, and the guardrails.

Step 1 — create the two collections #

Nothing exotic: two keyword fields are enough for a join to work.

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

PUT /orders
{
  "schema": { "dynamic": "strict", "properties": {
    "status": { "type": "keyword" },
    "org_id": { "type": "keyword" }
  }}
}

Step 2 — seed a known answer #

Two gold orgs, one silver, and ten orders spread over them:

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

Step 3 — join #

The URL stays on the outer collection (orders); the join clause names the inner one:

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"
    }
  }
}

9 hits — the three a-* orders and the six m-* orders, because org-a and org-mega are gold. b-1 is missing: org-b is silver. Change the inner term to silver and exactly b-1 comes back. The outer side never saw the tier — it only saw the resolved key set.

What actually executed #

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

Gateway — ran the inner term on orgs, distributed across its shards, and collected the matching _keys (org-a, org-mega). These join keys attach to the join node as a prepared payload.
Engine — each orders shard received the keys and ran the join as a local membership filter on org_id.

Two collections, one request, no application-side fan-out. 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.

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) 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 fast _ref column path below — that 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, composed 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"
        }}
      ]
    }
  }
}

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

Scoring and guardrails #

score_mode controls how inner-query scores reach the outer document: none (the default — pure filter, inner scores discarded), max, sum, or avg. In v1 only the _key rung carries scores; the field-targeted rungs are filter-shaped and reject other values with a 400.

The guardrails are fail-closed, not silent:

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, logged at preflight, never silently complete. Raise the cap if the dimension side is legitimately larger.
Point-in-time — a join under a PIT requires the PIT to cover both collections (same pinned snapshot on each side); otherwise a 400, not a pinned outer mixed with a realtime inner.
Namespace sharing — the inner query runs in the outer request’s namespace: one tenant’s authorization covers both collections.
400 for a missing target collection, a field absent from the outer schema, a non-keyword field, or a join contradicting a declared references; 503 when the target has no started primary shards.

Add "explain": true to a request and the response narrates the join plan — which locality tier it took and, when a better tier was skipped, why.

Part 2 — declaring the relationship #

The schema in Step 1 already works. Declaring what org_id means buys three things: joins that are checked (a contradicting request fails instead of silently joining wrong), a faster execution path, and FK-routed placement (an org’s orders colocate on one partition and become addressable by key). This part rebuilds orders with the declaration and walks each consequence — every step runs on your Lesson 1 node, and nothing outside this page is required.

Step 4 — rebuild `orders` with the declaration #

A collection’s schema is fixed at creation, so drop and recreate orders — only ten documents to rewrite:

DELETE /orders

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

Then re-run the ten order writes from Step 2 — orgs is untouched. What each parameter buys:

references — declares the foreign key: org_id names a document key in orgs. Writes do not fail when that document is missing (a dangling reference still lands; the join simply does not match it). The declaration also pins the join’s shape — Step 5 shows the 400.
immutable — a prerequisite of routing (a mutable routing key would re-route documents), enforced on the update path: an update that re-points or drops the field fails with a 400 — moving a document between routing values is a delete + re-create, never a silent drift.
ref — materialize the reference at write time into a hidden _<field>_ref numeric column that joins ride instead of resolving keys. Declare it before ingesting: only documents carrying a materialized _ref match (older documents need a reindex).
routing — placement routes by the field’s value: hash(org_id) % partitions picks the home partition, not the document key’s hash. Step 6 makes this visible.

Step 5 — the same join, checked and faster #

Re-run the Step 3 join verbatim — same 9 hits, different execution. With "ref": true the engine resolves org_id through the hidden _ref column instead of filtering terms. The full rung ladder: a field of "_key" rides the outer key registry; a references field with "ref": true rides the hidden column; any other declared keyword field runs engine-side as an exact terms filter — and a references without "ref": true still joins correctly, degrading to that keyword rung with a warning in the server log saying so. Add "explain": true and the response narrates the join plan — which locality tier it took, and why when a better one was skipped.

The orchestration keeps Part 1’s shape — gateway, then outer shards — but steps 2–4 changed vocabulary:

The declaration is enforced, fail-closed. The join’s target and join_on: "_key" must now match what org_id declares — point the join somewhere else and it is rejected rather than silently joining through a field the schema says means something else:

POST /orders/_search
{
  "size": 10,
  "query": {
    "join": {
      "target": "orders",
      "query": { "term": { "field": "status", "value": "new" } },
      "join_on": "_key",
      "field": "org_id"
    }
  }
}

400 — the join contradicts the declared references on org_id.

Step 6 — placement you can see in the write response #

Look back at the responses your Step 4 writes returned (or run one again):

{ "result": "created", "_id": "0,17" }

The system id is rolling,seq — two numbers; the exact values depend on your node’s write history. The home partition reads straight off the id: seq % partition_count. The routing value is what picks that partition — with routing: true a keyed write lands at hash(org_id) % partitions, and the seq is then allocated on that partition to match — so a-1, a-2, a-3 share one partition, b-1 lands on another, and the six m-* funnel into a third: an org’s orders are colocated, and the hot org’s dominance is already visible in the ids.

graph LR subgraph pa["partition of hash(org-a)"] a1("a-1") a2("a-2") a3("a-3") end subgraph pb["partition of hash(org-b)"] b1("b-1") end subgraph pm["partition of hash(org-mega)"] m1("m-1 … m-6") end

The routing field is load-bearing — a write without it has no home partition:

PUT /orders/_doc/bad-1 { "status": "x" }

400 — and null, arrays and objects are refused the same way.

Step 7 — by-key addressing with `?routing=` #

On a routed collection a key alone has no home partition either: key-addressed operations take the routing value:

GET /orders/_doc/a-1?routing=org-a     # 200 — the document
GET /orders/_doc/a-1                   # 400 — bare key, guidance in the body
GET /orders/_doc/a-1?routing=org-b     # 404 — honest: org-b's partition was probed

HEAD, UPDATE and DELETE take the same parameter:

POST /orders/_update/a-1?id_type=key&routing=org-a
{ "doc": { "status": "shipped" } }

The fetch reference and the partial update reference cover the full key-addressing vocabulary.

Step 8 — salting the hot org #

org-mega carries 6 of the 10 orders — all on one partition. Salting splits a hot routing value across a SET of partitions, and it is two-step by design: the feed measures, you assign.

Set a threshold, then read the feed:

PUT /orders/_salting
{ "threshold": 4, "assignments": {} }

GET /orders/_salting/feed?size=10

The feed is a terms aggregation over the routing field: org-mega (6 docs) crosses the threshold and is flagged candidate: true; org-a (3 docs) is not. Nothing is salted yet — candidate flagging is measurement, assignments are intent. Assign four splits:

PUT /orders/_salting
{ "threshold": 4, "assignments": { "org-mega": 4 } }

The response renders the computed partition set — the frozen engine contract hash("org-mega" #i) % partitions, sorted and deduped (the effective spread can land below the requested splits; the response reports what IS):

{
  "acknowledged": true,
  "routing_field": "org_id",
  "threshold": 4,
  "status": "assigned (salt-split routing active on writes)",
  "assignments": [ { "value": "org-mega", "splits": 4,
                     "partitions": [76, 83, 85, 254] } ]
}

The hot value’s placement, before and after the assignment:

graph LR subgraph before["before — every org-mega write funnels here"] HOT("org-mega's single partition
m-1 … m-6") end subgraph after["after — the assigned SET"] S1("partition 76") S2("partition 83") S3("partition 85") S4("partition 254") end HOT -.->|"new writes pick a member by hash(_key) % 4"| S1 HOT -.-> S2 HOT -.-> S3 HOT -.-> S4

Write six more orders for the hot org:

PUT /orders/_doc/m-7 { "status": "salted", "org_id": "org-mega" }   # … m-8 … m-12

The response _ids now spread across the assigned set — each keyed write picks its member deterministically (hash(value #hash(_key) % splits)), so the same key always lands in the same place while the org stops funneling every write into one partition. Unassigned values (org-a, org-b) keep their single partition, and key-addressed reads still work — they probe the whole set:

GET /orders/_doc/m-3?routing=org-mega

Step 9 — placement changed, visibility did not #

Re-run the Step 3 join: 15 hits now — the three a-* orders plus twelve m-*. Search fans out across every partition, so the split placement is invisible to queries; salting changes placement, never join visibility.

Clearing an assignment narrows placement on the very next write. The PUT is full-replace — restate whatever stays:

PUT /orders/_salting
{ "threshold": 4, "assignments": {} }

Documents written before an assignment stay on their old partitions until a rehash migration moves them — the salting reference covers the contract, the migration story, and the limits.

What you learned #

join is a semi-join: the inner query resolves to a key set, the outer collection filters on it — two collections, one request, no application-side fan-out.
The inner side is any query; join_on can be _key or any inner field (values, not keys).
Joins belong in must/filter; they are inert under should/must_not and cannot nest.
Guardrails fail closed: capped joins report lower bounds, PITs must cover both sides, malformed shapes are 400s.
Declaring references/immutable/ref/routing buys checked joins (fail-closed 400s), the hidden _ref column path, and FK-routed placement with ?routing= key addressing — and salting splits a hot value across a partition set without touching join visibility. Part 2 walked all of it against your own node.

Next: Lesson 9 — operations.