cross-join

What it does

In noy-db, records are encrypted before they reach any storage backend, and queries run inside the hub, after decryption — the store only ever sees ciphertext. .crossJoin(target, { as }) is the query clause that pairs rows across two collections in the same vault without any declared foreign key: every left row is combined with every right row (a Cartesian product), and each result row carries the original left fields plus the right row under the alias you choose. An optional on: filter turns it into a lateral join — each left row is paired only with the right rows that match it.

You want it for "every A × every B" shapes: active workers per pay period, coverage per billing cycle, every product × every region before an aggregation. For lookups that follow a declared reference (invoice → its client), use joins instead — a cross-join is the tool when no ref() relationship exists between the two collections.

Cross-join is part of the core query builder — there is no with*() factory and no bundle cost to opt into. A cost ceiling (default 50,000 result rows) protects you from accidentally materializing a huge product; it throws before allocating the result.

Setup

No opt-in is needed for .crossJoin() itself — it is available on every collection.query(). The grouped examples below also use the aggregate service (groupBy + reducers), which is opt-in:

import { createNoydb } from '@noy-db/hub'
import { withReduce } from '@noy-db/hub/reduce'
import { toMemory } from '@noy-db/to-memory'

const db = await createNoydb({
  store: toMemory(),
  user: 'alice',
  secret: 'a-strong-secret',
  reduceStrategy: withReduce(),
})

Usage

The flows below share this setup — a vault with pay periods and workers (no ref() between them):

type Period = { quarter: string; start: string; end: string }
type Worker = { name: string; since: string; until: string | null }

const vault = await db.openVault('acme')
const periods = vault.collection<Period>('periods')
const workers = vault.collection<Worker>('workers')

await periods.put('q1', { quarter: 'q1', start: '2026-01', end: '2026-03' })
await periods.put('q2', { quarter: 'q2', start: '2026-04', end: '2026-06' })

await workers.put('w1', { name: 'Alice', since: '2025-12', until: null })
await workers.put('w2', { name: 'Bob',   since: '2026-01', until: '2026-06' })
await workers.put('w3', { name: 'Carol', since: '2026-04', until: null })

Full Cartesian product

Without on:, every left row is paired with every right row. Each result row is the left record plus the right record under the as alias:

const pairs = periods.query()
  .crossJoin<Worker, 'worker'>('workers', { as: 'worker' })
  .toArray()
// → 6 rows (2 periods × 3 workers), each shaped like:

[
  { "quarter": "q1", "start": "2026-01", "end": "2026-03",
    "worker": { "name": "Alice", "since": "2025-12", "until": null } },
  { "quarter": "q1", "start": "2026-01", "end": "2026-03",
    "worker": { "name": "Bob", "since": "2026-01", "until": "2026-06" } }
]

Lateral join + grouped aggregate — active workers per period

Passing on: (left) => (right) => boolean filters the right side per left row. Combined with groupBy and a count() reducer, this answers "how many workers were active in each pay period":

import { count } from '@noy-db/hub/reduce'

const perPeriod = periods.query()
  .crossJoin<Worker, 'worker'>('workers', {
    as: 'worker',
    on: (period) => (worker) =>
      worker.since <= period.start &&
      (worker.until === null || worker.until >= period.end),
  })
  .groupBy('quarter')
  .aggregate({ workerCount: count() })
  .run()

[
  { "quarter": "q1", "workerCount": 2 },
  { "quarter": "q2", "workerCount": 3 }
]

The on: filter also has a subset form — on: (left) => TTarget[] returns the exact right rows to pair with each left row, skipping the per-row scan of the whole right side when you can compute the subset directly.

Clause order matters

Cross-join clauses are interleaved with where/filter clauses and processed in declaration order:

• .where(...) before .crossJoin(...) filters the left side first — fewer left rows means a smaller product, so put left-side filters first whenever you can.
• .where(...) after .crossJoin(...) runs on the expanded rows and can reference the aliased right-side fields ('worker.name').

// Left filter first: expands only q1's pairs (3 rows, not 6)
periods.query()
  .where('quarter', '==', 'q1')
  .crossJoin<Worker, 'worker'>('workers', { as: 'worker' })
  .toArray()
// → 3 rows: q1 × Alice, q1 × Bob, q1 × Carol

// Alias filter after: expands all 6 pairs, keeps Alice's
periods.query()
  .crossJoin<Worker, 'worker'>('workers', { as: 'worker' })
  .where('worker.name', '==', 'Alice')
  .toArray()
// → 2 rows: q1 × Alice, q2 × Alice

Live queries

.live() on a cross-join query subscribes to the left collection and every cross-join target (deduplicated by collection name). A mutation on either side re-runs the query and notifies subscribers. Always call live.stop() when finished. See live for the reactive primitive itself.

const live = await periods.query()
  .crossJoin<Worker, 'worker'>('workers', { as: 'worker' })
  .live()

live.subscribe(() => console.log('pairs now:', live.value.length))

await workers.put('w4', { name: 'Dan', since: '2026-06', until: null })
// → "pairs now: 8"  (right-side mutation re-fired the query: 2 × 4)

live.stop()

Inside materialized views

A cross-join inside a materialized view's query() callback participates in the view's queryHash — the fingerprint of the query structure that tells the view when its definition changed and its rows must be refreshed. An inline on: callback cannot be fingerprinted, so it disables that change detection for the cross-join (a console warning says so at build time). Use the named-predicate form instead: declare the predicate in the view's predicates map (each entry pairs a consumer-stable hash with the function) and reference it by name:

// inside a withMaterializedView definition:
predicates: {
  activeInPeriod: {
    hash: 'activeInPeriod-v1',
    fn: (row: Period & { worker: Worker }) =>
      row.worker.since <= row.start &&
      (row.worker.until === null || row.worker.until >= row.end),
  },
},
query: (v) =>
  v.collection<Period>('periods').query()
    .crossJoin<Worker, 'worker'>('workers', {
      as: 'worker',
      on: { predicate: 'activeInPeriod' },
    }),

The predicate function receives the expanded row (left fields plus the alias field). Bump the hash whenever the function's semantics change — that folds into the queryHash and forces a refresh. The { predicate } form throws outside a view that declares a predicates map, and throws if the name is not registered.

API

query.crossJoin(target, opts)

crossJoin<TTarget, As extends string>(
  target: string,
  opts: {
    as: As
    on?: ((left: T) => TTarget[] | ((right: TTarget) => boolean))
       | { predicate: string }
    maxRows?: number
  }
): Query<T & { [K in As]: TTarget }>

T is the left collection's record type — the query must come from collection.query() (a bare new Query(...) has no join context and .crossJoin() throws). Returns a new immutable Query whose row type gains the alias field; the original query is unchanged.

on: shapes

Errors

Both are importable from @noy-db/hub:

• CrossJoinTooLargeError (code CROSS_JOIN_TOO_LARGE) — the product would exceed the ceiling. Thrown before the result is allocated. Carries target, expected (the row count that would have been produced), and limit. Narrow the left side with .where() first, or raise the ceiling with { maxRows }.
• CrossJoinSourceUnknownError (code CROSS_JOIN_SOURCE_UNKNOWN) — the target collection is not known in the vault at execution time. Carries target and leftCollection.

Limits & gotchas

• Row ceiling, checked before allocation. For a full Cartesian join, leftRows × rightRows is compared to the ceiling up front. For the lateral form, the ceiling is charged cumulatively — it applies to the total post-filter row count across all left rows, not per left row.
• count() counts expanded rows. On a cross-join query, query.count() returns the size of the expanded relation (left × right after on:), not the left-side cardinality.
• Same vault only. Both collections must be open in the same vault, or execution throws CrossJoinSourceUnknownError. For reads that span vaults, use db.queryAcross() — see query basics.
• Plan-only execution is unsupported. The standalone executePlan() helper throws on plans containing cross-join clauses — execute through collection.query()…toArray(), which carries the join context.
• Not usable as a retrieve scope. A query containing cross-join clauses cannot be passed as the within scope of collection.retrieve() (see search).
• Inline on: inside a materialized view disables queryHash change detection for that cross-join and emits a build-time console warning — use on: { predicate: 'name' } there.

Left/outer cross-join variants and cross-joins over materialized-view virtual collections are not available.

See also

• joins — FK joins over declared ref() relationships (use these when a reference exists)
• aggregate — groupBy + reducers over the expanded rows
• live — the reactive query primitive .live() returns
• materialized-views — named predicates and queryHash
• Query basics — where / filter / orderBy and db.queryAcross()
• Proven by showcase 92-with-cross-join.