# Pipeline builder reference (/docs/orm/next/reference/pipeline-builder)

> For the complete Prisma documentation index, see [llms.txt](https://www.prisma.io/docs/llms.txt). A markdown version of any docs page is available by appending `.md` to its URL.

Reference for the Prisma Next MongoDB pipeline builder's stages, accumulators, expression helpers, and write terminals.

Location: ORM > Next > Reference > Pipeline builder reference

The pipeline builder gives you a typed way to build MongoDB aggregation pipelines. You reach it through `db.query`, chain aggregation stages onto a starting collection, and resolve the pipeline with a terminal. It is the MongoDB counterpart to the [SQL query builder](https://www.prisma.io/docs/orm/next/reference/sql-query-builder): a lower-level surface for the queries the [ORM client](https://www.prisma.io/docs/orm/next/reference/orm-client) doesn't express.

The pipeline builder is aggregation-only by design. There is no `find` and no `distinct`; to fetch a single document, filter and then `limit(1)`. Everything the builder produces compiles to a MongoDB aggregation pipeline.

Use the ORM client (`db.orm`) for everyday reads and writes across models and relations. Drop down to the pipeline builder when you need a stage the ORM client doesn't expose: grouping, `$lookup` joins with post-processing, computed projections, multi-stage transformations, or aggregation-time writes with `$out` / `$merge`.

This page documents every stage, accumulator, expression helper, and write terminal, with the behavior that the executable test suite confirmed and the caveats it surfaced. Where a method is not executable in the validation harness, the Remarks say so.

> [!NOTE]
> This is the MongoDB pipeline builder
> 
> The pipeline builder targets MongoDB only. PostgreSQL has no pipeline builder: use the [SQL query builder](https://www.prisma.io/docs/orm/next/reference/sql-query-builder) for PostgreSQL joins and aggregates, and the [ORM client](https://www.prisma.io/docs/orm/next/reference/orm-client) for everyday queries on either database.

## Example schema [#example-schema]

All examples on this page run against the following schema, and every example is transcribed from an executable test suite that runs against a live MongoDB database. `Post` is a discriminated model (`Article` and `Tutorial` variants share the `posts` collection, discriminated by `kind`).

**Expand for the example schema**

```prisma
enum UserRole {
  @@type("mongo/string@1")
  Admin  = "admin"
  Author = "author"
  Reader = "reader"
}

type Address {
  street  String
  city    String
  zip     String?
  country String
}

model User {
  id      ObjectId @id @map("_id")
  name    String
  email   String
  bio     String?
  role    UserRole
  address Address?
  posts   Post[]
  @@map("users")
}

model Post {
  id        ObjectId @id @map("_id")
  title     String
  content   String
  kind      String
  authorId  ObjectId
  createdAt DateTime
  author    User @relation(fields: [authorId], references: [id])
  @@discriminator(kind)
  @@index([authorId])
  @@index([createdAt(sort: Desc), authorId])
  @@map("posts")
}

model Article {
  summary   String
  @@base(Post, "article")
  @@unique([summary])
}

model Tutorial {
  difficulty String
  duration   Int
  @@base(Post, "tutorial")
}
```

## Entry points [#entry-points]

Every pipeline starts with `db.query.from(root)` and ends with a terminal. Between them you chain stages.

### from() [#from]

Enter the pipeline builder on a collection.

#### Remarks [#remarks]

* MongoDB only. `db.query` is the pipeline-builder entry point on the client, the same object the standalone `mongoQuery(...)` returns.
* `from(root)` takes a contract **root name**: the lowercase plural collection name from the contract's `roots` map (`'posts'`, `'users'`), the same names the ORM client uses (`db.orm.users`), not the PSL model names.
* Passing an unknown root throws synchronously: `Error: Unknown root: "<name>". Valid roots: ...`. This is an `Error`, not a `TypeError`.
* `from()` returns a builder you chain stages onto. The builder moves through three states as you chain: a starting collection, a filtered collection after `match()`, and a pipeline chain after any other stage. Each state exposes the methods that are valid at that point (for example, the root-level writes `insertOne` / `insertMany` are only available before you add stages).

#### Options [#options]

| Name   | Type                                  | Required | Description                       |
| ------ | ------------------------------------- | -------- | --------------------------------- |
| `root` | Contract root name (`string` literal) | Yes      | The collection to aggregate over. |

#### Return type [#return-type]

| Return type      | Example                  | Description                                     |
| ---------------- | ------------------------ | ----------------------------------------------- |
| Pipeline builder | `db.query.from('posts')` | A builder you chain stages and a terminal onto. |

#### Examples [#examples]

##### Enter the builder on a collection [#enter-the-builder-on-a-collection]

```typescript
const plan = db.query.from('posts').build();
const posts = await db.execute(plan);
```

### Building and executing a pipeline [#building-and-executing-a-pipeline]

A pipeline is inert until you execute it. `build()` (or its alias `aggregate()`) turns the chain into a plan; `db.execute(plan)` runs it.

#### Remarks [#remarks-1]

* `db.execute(plan)` is the client-level executor. It accepts any plan, including one built by the pipeline builder. If you hold a connected runtime directly, `runtime.execute(plan)` is the equivalent lower-level call.
* **Read results are codec-decoded.** Unlike [raw queries](https://www.prisma.io/docs/orm/next/reference/raw-queries), a pipeline built through `db.query` carries a result shape, so top-level `_id` fields come back as decoded hex strings, and other fields are decoded to their contract types.
* A sub-document pulled in by `lookup()` is **not** decoded the same way: its `_id` comes back as a raw driver `ObjectId`. Compare it with `String(...)`. This matches the ORM client's `include()` behavior.

#### Examples [#examples-1]

##### Execute through the client [#execute-through-the-client]

```typescript
const plan = db.query.from('posts').sort({ createdAt: 1 }).build();
const posts = await db.execute(plan);
```

## Pipeline stages [#pipeline-stages]

Stages transform the documents flowing through the pipeline. Chain them in order; each stage's output feeds the next. Field-accessor callbacks (`(f) => ...`) reference the current document's fields.

### match() [#match]

Filter documents by a predicate.

#### Remarks [#remarks-2]

* `match()` takes a callback that receives a field accessor and returns a filter expression (`(f) => f.kind.eq('tutorial')`).
* `match()` filter values are **not codec-encoded**. The value you pass is compared as-is against the stored value. For most scalar fields this is what you want, but it breaks down for `_id` (see the warning below).
* Leaf fields expose these filter operators: `eq`, `ne`, `gt`, `gte`, `lt`, `lte`, `in`, `nin`, `exists`, and `type`. `eq` and `expr(...)`-wrapped comparisons are exercised by our executable test suite; the remaining operators are documented from the builder's source (`field-accessor.ts`) and follow the matching MongoDB query operators (`$ne`, `$gt`, `$in`, `$exists`, `$type`, and so on).
* For an aggregation-expression predicate (comparing computed values rather than a field against a constant), wrap it in `expr(...)`: `match((f) => expr(fn.gt(fn.year(f.createdAt), fn.literal(2023))))`. See [Expression helpers](#expression-helpers).

#### Options [#options-1]

| Name        | Type                               | Required | Description                           |
| ----------- | ---------------------------------- | -------- | ------------------------------------- |
| `predicate` | Callback `(f) => FilterExpression` | Yes      | The condition documents must satisfy. |

#### Return type [#return-type-1]

| Return type      | Example                             | Description                                                                                        |
| ---------------- | ----------------------------------- | -------------------------------------------------------------------------------------------------- |
| Filtered builder | `db.query.from('posts').match(...)` | A builder narrowed by the filter, chainable into more stages, write terminals, or a read terminal. |

#### Examples [#examples-2]

##### Filter by a field [#filter-by-a-field]

```typescript
const plan = db.query
  .from('posts')
  .match((f) => f.kind.eq('tutorial'))
  .build();
const tutorials = await db.execute(plan);
```

##### Aggregation-expression predicate [#aggregation-expression-predicate]

```typescript
import { fn, expr } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .match((f) => expr(fn.gt(fn.year(f.createdAt), fn.literal(2023))))
  .build();
const recent = await db.execute(plan);
```

> [!WARNING]
> `_id`
> 
>  equality filters do not work through the pipeline builder
> 
> Filtering by `_id` equality inside `match()` **never matches any document**, and this is verified empirically. Neither a hex string (`f._id.eq('507f...')`) nor a real driver `ObjectId` matches: the pipeline builder's value resolver walks the value as a plain object, destructuring an `ObjectId` into its internal `buffer` bytes, so the filter that reaches MongoDB never carries a real `ObjectId`. Note that the hex-string form **compiles without any cast** — round-tripping a decoded `_id` back into `match()` typechecks cleanly and silently returns zero rows. Only the driver-`ObjectId` form requires a cast to attempt.
> 
> There is currently no supported way to filter by `_id` equality through the typed pipeline builder. The escape hatch is [`rawCommand()`](#rawcommand), which places a real `ObjectId` directly in a raw pipeline document and matches correctly, or the [ORM client](https://www.prisma.io/docs/orm/next/reference/orm-client), which handles `_id` encoding for you (`db.orm.posts.where({ _id })`).

### sort() [#sort]

Order documents by a field spec.

#### Remarks [#remarks-3]

* `sort()` takes a plain object spec: `{ field: 1 }` ascending, `{ field: -1 }` descending. Multiple keys sort in the order they appear.

#### Options [#options-2]

| Name   | Type                   | Required | Description                       |
| ------ | ---------------------- | -------- | --------------------------------- |
| `spec` | `{ [field]: 1 \| -1 }` | Yes      | The sort key(s) and direction(s). |

#### Return type [#return-type-2]

| Return type      | Example                                          | Description                         |
| ---------------- | ------------------------------------------------ | ----------------------------------- |
| Pipeline builder | `db.query.from('posts').sort({ createdAt: -1 })` | A builder with an ordering applied. |

#### Examples [#examples-3]

##### Sort descending [#sort-descending]

```typescript
const plan = db.query.from('posts').sort({ createdAt: -1 }).build();
const newestFirst = await db.execute(plan);
```

### limit() [#limit]

Cap the number of documents.

#### Remarks [#remarks-4]

* Combine `sort()` then `limit(1)` to fetch a single document: the pipeline builder has no `first()`.

#### Options [#options-3]

| Name    | Type     | Required | Description                            |
| ------- | -------- | -------- | -------------------------------------- |
| `count` | `number` | Yes      | Maximum number of documents to return. |

#### Return type [#return-type-3]

| Return type      | Example                           | Description                             |
| ---------------- | --------------------------------- | --------------------------------------- |
| Pipeline builder | `db.query.from('posts').limit(1)` | A builder limited to `count` documents. |

#### Examples [#examples-4]

##### Fetch a single document [#fetch-a-single-document]

```typescript
const plan = db.query.from('posts').sort({ createdAt: 1 }).limit(1).build();
const [oldest] = await db.execute(plan);
```

### skip() [#skip]

Offset into the sorted result set.

#### Options [#options-4]

| Name    | Type     | Required | Description                  |
| ------- | -------- | -------- | ---------------------------- |
| `count` | `number` | Yes      | Number of documents to skip. |

#### Return type [#return-type-4]

| Return type      | Example                          | Description                            |
| ---------------- | -------------------------------- | -------------------------------------- |
| Pipeline builder | `db.query.from('posts').skip(1)` | A builder offset by `count` documents. |

#### Examples [#examples-5]

##### Paginate [#paginate]

```typescript
const plan = db.query.from('posts').sort({ createdAt: 1 }).skip(1).build();
const afterFirst = await db.execute(plan);
```

### sample() [#sample]

Draw a random subset of documents.

#### Options [#options-5]

| Name   | Type     | Required | Description                    |
| ------ | -------- | -------- | ------------------------------ |
| `size` | `number` | Yes      | Number of documents to sample. |

#### Return type [#return-type-5]

| Return type      | Example                            | Description                         |
| ---------------- | ---------------------------------- | ----------------------------------- |
| Pipeline builder | `db.query.from('posts').sample(1)` | A builder emitting a random subset. |

#### Examples [#examples-6]

##### Random subset [#random-subset]

```typescript
const plan = db.query.from('posts').sample(1).build();
const oneRandom = await db.execute(plan);
```

### addFields() [#addfields]

Compute new fields and attach them to each document.

#### Remarks [#remarks-5]

* `addFields()` takes a callback returning an object of new field names to computed [expression-helper](#expression-helpers) values. Existing fields are preserved.

#### Options [#options-6]

| Name   | Type                                        | Required | Description                |
| ------ | ------------------------------------------- | -------- | -------------------------- |
| `spec` | Callback `(f) => ({ [field]: Expression })` | Yes      | The new fields to compute. |

#### Return type [#return-type-6]

| Return type      | Example                                 | Description                                     |
| ---------------- | --------------------------------------- | ----------------------------------------------- |
| Pipeline builder | `db.query.from('posts').addFields(...)` | A builder whose documents carry the new fields. |

#### Examples [#examples-7]

##### Attach a computed field [#attach-a-computed-field]

```typescript
import { fn } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .addFields((f) => ({ shoutTitle: fn.toUpper(f.title) }))
  .build();
const withShout = await db.execute(plan);
```

### lookup() [#lookup]

Join documents from another collection (`$lookup`).

#### Remarks [#remarks-6]

* `lookup()` takes a callback that builds the join with `from(root).on((local, foreign) => ({ local, foreign })).as(name)`: the foreign collection, the local and foreign fields to match on, and the output array field name.
* The joined documents land in an array under the `as` name.
* A looked-up sub-document's `_id` comes back as a raw driver `ObjectId`, not a decoded hex string. Compare it with `String(...)`.

#### Options [#options-7]

| Name      | Type                                             | Required | Description             |
| --------- | ------------------------------------------------ | -------- | ----------------------- |
| `builder` | Callback `(from) => from(root).on(...).as(name)` | Yes      | The join specification. |

#### Return type [#return-type-7]

| Return type      | Example                              | Description                                       |
| ---------------- | ------------------------------------ | ------------------------------------------------- |
| Pipeline builder | `db.query.from('posts').lookup(...)` | A builder whose documents carry the joined array. |

#### Examples [#examples-8]

##### Join a foreign collection [#join-a-foreign-collection]

```typescript
const plan = db.query
  .from('posts')
  .match((f) => f.title.eq('Hello world'))
  .lookup((from) =>
    from('users')
      .on((local, foreign) => ({ local: local.authorId, foreign: foreign._id }))
      .as('author'),
  )
  .build();
const withAuthor = await db.execute(plan);
// withAuthor[0].author is an array; author[0]._id is a raw ObjectId — compare with String(...)
```

### project() [#project]

Reshape each document.

#### Remarks [#remarks-7]

* `project()` has two forms. A **key-list** form narrows to the named fields (`project('title', 'kind')`); `_id` is retained implicitly even when not listed.
* A **callback** form computes a projection spec (`project((f) => ({ title: 1, shout: fn.toUpper(f.title) }))`), keeping, dropping, or computing each field.

#### Options [#options-8]

| Name        | Type                                                  | Required      | Description                   |
| ----------- | ----------------------------------------------------- | ------------- | ----------------------------- |
| `...fields` | Field names (`string`)                                | Key-list form | The fields to keep.           |
| `spec`      | Callback `(f) => ({ [field]: 1 \| 0 \| Expression })` | Callback form | The projection specification. |

#### Return type [#return-type-8]

| Return type      | Example                                           | Description                             |
| ---------------- | ------------------------------------------------- | --------------------------------------- |
| Pipeline builder | `db.query.from('posts').project('title', 'kind')` | A builder projected to the given shape. |

#### Examples [#examples-9]

##### Key-list form [#key-list-form]

```typescript
const plan = db.query.from('posts').project('title', 'kind').build();
const trimmed = await db.execute(plan);
// each document has title, kind, and _id — no content
```

##### Callback form [#callback-form]

```typescript
import { fn } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .project((f) => ({ title: 1, shout: fn.toUpper(f.title) }))
  .build();
const projected = await db.execute(plan);
```

### unwind() [#unwind]

Unroll an array field into one document per element.

#### Remarks [#remarks-8]

* `unwind(field, { preserveNullAndEmptyArrays? })` maps to MongoDB's `$unwind`. It requires an **array-typed** field to unroll.
* This stage is documented from its signature only: the example schema has no array field to unwind, so `unwind()` is not exercised by the validation harness. Verify it against a real array field in your own schema before relying on it.

#### Options [#options-9]

| Name                                 | Type                  | Required | Description                                                                 |
| ------------------------------------ | --------------------- | -------- | --------------------------------------------------------------------------- |
| `field`                              | Field name (`string`) | Yes      | The array field to unroll.                                                  |
| `options.preserveNullAndEmptyArrays` | `boolean`             | No       | Keep documents whose array is null, missing, or empty. Defaults to `false`. |

#### Return type [#return-type-9]

| Return type      | Example                               | Description                                        |
| ---------------- | ------------------------------------- | -------------------------------------------------- |
| Pipeline builder | `db.query.from(root).unwind('items')` | A builder emitting one document per array element. |

### group() [#group]

Group documents by a key and compute per-group aggregates.

#### Remarks [#remarks-9]

* `group()` takes a callback that receives **only** the field accessor (`(f) => ...`) and returns a spec object. The spec's `_id` sets the grouping key; every other key must be an **accumulator**.
* Accumulators come from the imported `acc` namespace, **not** a second callback argument: `import { acc } from '@prisma-next/mongo-query-builder'`, then `acc.count()`, `acc.push(f.title)`. The callback signature is single-argument; there is no `(f, acc) => ...` form. (Some older internal material shows a two-argument callback. That is incorrect.)
* A `_id: null` key groups the whole collection into a single bucket.
* A non-accumulator value for a non-`_id` key is a compile error. Forced past the type system, `group()` throws at build time: `... must use an accumulator (e.g. acc.sum(), acc.count()) ...`.

#### Options [#options-10]

| Name   | Type                                                               | Required | Description                                  |
| ------ | ------------------------------------------------------------------ | -------- | -------------------------------------------- |
| `spec` | Callback `(f) => ({ _id: keyExpression \| null, [alias]: acc.* })` | Yes      | The grouping key and per-group accumulators. |

#### Return type [#return-type-10]

| Return type      | Example                             | Description                                |
| ---------------- | ----------------------------------- | ------------------------------------------ |
| Pipeline builder | `db.query.from('posts').group(...)` | A builder emitting one document per group. |

#### Examples [#examples-10]

##### Group by a field [#group-by-a-field]

```typescript
import { acc } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .group((f) => ({
    _id: f.authorId,
    postCount: acc.count(),
    titles: acc.push(f.title),
  }))
  .build();
const perAuthor = await db.execute(plan);
```

##### Group the whole collection [#group-the-whole-collection]

```typescript
import { acc } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .group((f) => ({ _id: null, total: acc.count(), latest: acc.max(f.createdAt) }))
  .build();
const [summary] = await db.execute(plan);
// { _id: null, total: 2, latest: <Date> }
```

For Prisma 7 users, `groupBy` becomes a `group()` stage on the pipeline builder:

```diff
- const perAuthor = await prisma.post.groupBy({ by: ['authorId'], _count: true });
+ const perAuthor = await db.execute(
+   db.query.from('posts').group((f) => ({ _id: f.authorId, postCount: acc.count() })).build(),
+ );
```

### replaceRoot() [#replaceroot]

Promote a computed sub-document to the top level.

#### Remarks [#remarks-10]

* `replaceRoot()` takes a callback returning a **document-shaped** expression. A bare scalar is rejected by MongoDB at runtime (`'newRoot' expression must evaluate to an object`).
* A common pattern is to promote the first element of a `lookup()` array with `fn.arrayElemAt(f.author, fn.literal(0))`.

#### Options [#options-11]

| Name   | Type                                 | Required | Description                          |
| ------ | ------------------------------------ | -------- | ------------------------------------ |
| `spec` | Callback `(f) => documentExpression` | Yes      | The document to promote to the root. |

#### Return type [#return-type-11]

| Return type      | Example                                   | Description                                              |
| ---------------- | ----------------------------------------- | -------------------------------------------------------- |
| Pipeline builder | `db.query.from('posts').replaceRoot(...)` | A builder whose documents are the promoted sub-document. |

#### Examples [#examples-11]

##### Promote a looked-up document [#promote-a-looked-up-document]

```typescript
import { fn } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .match((f) => f.title.eq('Hello world'))
  .lookup((from) =>
    from('users')
      .on((local, foreign) => ({ local: local.authorId, foreign: foreign._id }))
      .as('author'),
  )
  .replaceRoot((f) => fn.arrayElemAt(f.author, fn.literal(0)))
  .build();
const authors = await db.execute(plan);
// each document is the promoted author sub-document
```

### count() [#count]

Reduce the pipeline to a single document holding the count.

#### Options [#options-12]

| Name    | Type     | Required | Description                          |
| ------- | -------- | -------- | ------------------------------------ |
| `field` | `string` | Yes      | The output field name for the count. |

#### Return type [#return-type-12]

| Return type      | Example                                 | Description                                       |
| ---------------- | --------------------------------------- | ------------------------------------------------- |
| Pipeline builder | `db.query.from('posts').count('total')` | A builder emitting one `{ [field]: n }` document. |

#### Examples [#examples-12]

##### Count documents [#count-documents]

```typescript
const plan = db.query.from('posts').count('total').build();
const [{ total }] = await db.execute(plan);
// { total: 2 }
```

### sortByCount() [#sortbycount]

Group by an expression and sort descending by group size.

#### Remarks [#remarks-11]

* `sortByCount()` takes a callback returning the grouping expression. It emits one document per distinct value, each `{ _id, count }`, sorted by `count` descending.

#### Options [#options-13]

| Name         | Type                         | Required | Description                      |
| ------------ | ---------------------------- | -------- | -------------------------------- |
| `expression` | Callback `(f) => expression` | Yes      | The value to group and count by. |

#### Return type [#return-type-13]

| Return type      | Example                                             | Description                                    |
| ---------------- | --------------------------------------------------- | ---------------------------------------------- |
| Pipeline builder | `db.query.from('posts').sortByCount((f) => f.kind)` | A builder emitting `{ _id, count }` documents. |

#### Examples [#examples-13]

##### Count occurrences of a field value [#count-occurrences-of-a-field-value]

```typescript
const plan = db.query
  .from('posts')
  .sortByCount((f) => f.kind)
  .build();
const byKind = await db.execute(plan);
// two buckets, each { _id: <kind>, count: 1 } — order of tied counts is not guaranteed
```

### redact() [#redact]

Keep or prune documents (and sub-documents) based on an expression.

#### Remarks [#remarks-12]

* `redact()` maps to MongoDB's `$redact`, whose expression must evaluate to one of the system variables `$$KEEP`, `$$DESCEND`, or `$$PRUNE`.
* This is a sharp edge: `fn.literal('KEEP')` does **not** work, because `fn.literal(...)` compiles to a plain string via `$literal`, which `$redact` rejects at the server. There is no dedicated expression helper for a system-variable reference. Emitting `$$KEEP` through the typed builder requires an internal trick (a field-accessor path that itself starts with `$`), which is not a supported public pattern.
* For anything beyond a trivial redact, prefer [`rawCommand()`](#rawcommand), where you can write the `$redact` stage with `$$KEEP` / `$$PRUNE` directly.

### Option-object stages [#option-object-stages]

Several stages take a single MongoDB stage options object rather than a typed field-accessor callback. These map closely to the underlying MongoDB stage documents.

#### Remarks [#remarks-13]

* The following stages take an options object: `unionWith`, `bucket`, `bucketAuto`, `geoNear`, `facet`, `graphLookup`, `setWindowFields`, `densify`, and `fill`.
* Of these, `unionWith`, `bucket`, and `graphLookup` are exercised by our executable test suite (examples below). `bucketAuto`, `geoNear`, `facet`, `setWindowFields`, `densify`, and `fill` are documented from the builder's source signatures and are **not exercised by the validation harness**.
* **The expression fields inside these options are opaque.** Fields like `bucket.groupBy`, `graphLookup.startWith`, `setWindowFields.partitionBy`, and `geoNear.near` are raw aggregation expressions (`MongoAggExpr`), not reachable through the typed field-accessor callback the classic stages use. You build these values with expression helpers and pass the underlying node (`fn.year(ref).node`), which is less type-safe than the callback stages. This is a known gap in the typed surface for these stages.
* `unionWith` is the simplest: it takes a collection name and concatenates that collection's documents onto the pipeline output.

#### Examples [#examples-14]

##### Concatenate another collection with unionWith() [#concatenate-another-collection-with-unionwith]

```typescript
const plan = db.query.from('posts').unionWith('users').build();
const combined = await db.execute(plan);
// posts followed by users
```

`bucket()` and `graphLookup()` are executable but require hand-built expression nodes for their opaque fields (`bucket.groupBy`, `graphLookup.startWith`). Build those nodes with expression helpers and pass `.node`. Because the value bypasses the typed accessor, verify the stage output against your own data before relying on it.

### Atlas-only stages [#atlas-only-stages]

`search()`, `searchMeta()`, and `vectorSearch()` build MongoDB Atlas Search stages (`$search`, `$searchMeta`, `$vectorSearch`).

#### Remarks [#remarks-14]

* These stages **require MongoDB Atlas** (they need Atlas Search's `mongot` process) and are **not validated by our executable test suite**, which runs against an in-memory MongoDB with no Atlas Search backend. Only their plan shape is verified; the stages are never executed.
* `search(spec)` and `searchMeta(spec)` take an Atlas Search operator spec (for example `{ text: { query: 'hello', path: 'title' } }`).
* `vectorSearch(spec)` takes an Atlas Vector Search spec (`{ index, path, queryVector, numCandidates, limit }`).
* Verify these against a real Atlas deployment before relying on them.

#### Return type [#return-type-14]

| Return type      | Example                                                                      | Description                                                           |
| ---------------- | ---------------------------------------------------------------------------- | --------------------------------------------------------------------- |
| Pipeline builder | `db.query.from('posts').search({ text: { query: 'hello', path: 'title' } })` | A builder with an Atlas Search stage. Executes only on MongoDB Atlas. |

## Accumulators [#accumulators]

Accumulators compute per-group values inside a [`group()`](#group) stage. Import them from `@prisma-next/mongo-query-builder`:

```typescript
import { acc } from '@prisma-next/mongo-query-builder';
```

The builder exposes all nineteen MongoDB accumulators:

| Accumulator                          | Signature                        | Description                                          |
| ------------------------------------ | -------------------------------- | ---------------------------------------------------- |
| `acc.count()`                        | `count()`                        | Number of documents in the group.                    |
| `acc.sum(expr)`                      | `sum(expression)`                | Sum of the expression across the group.              |
| `acc.avg(expr)`                      | `avg(expression)`                | Average of the expression.                           |
| `acc.min(expr)`                      | `min(expression)`                | Minimum value.                                       |
| `acc.max(expr)`                      | `max(expression)`                | Maximum value.                                       |
| `acc.first(expr)`                    | `first(expression)`              | Value from the first document in the group.          |
| `acc.last(expr)`                     | `last(expression)`               | Value from the last document in the group.           |
| `acc.push(expr)`                     | `push(expression)`               | Array of the expression's value from every document. |
| `acc.addToSet(expr)`                 | `addToSet(expression)`           | Array of distinct values.                            |
| `acc.firstN({ input, n })`           | `firstN({ input, n })`           | First `n` values.                                    |
| `acc.lastN({ input, n })`            | `lastN({ input, n })`            | Last `n` values.                                     |
| `acc.maxN({ input, n })`             | `maxN({ input, n })`             | Top `n` values by value.                             |
| `acc.minN({ input, n })`             | `minN({ input, n })`             | Bottom `n` values by value.                          |
| `acc.top({ output, sortBy })`        | `top({ output, sortBy })`        | Single document by a sort order.                     |
| `acc.bottom({ output, sortBy })`     | `bottom({ output, sortBy })`     | Single document by the reverse sort order.           |
| `acc.topN({ output, sortBy, n })`    | `topN({ output, sortBy, n })`    | Top `n` documents by a sort order.                   |
| `acc.bottomN({ output, sortBy, n })` | `bottomN({ output, sortBy, n })` | Bottom `n` documents by a sort order.                |
| `acc.stdDevPop(expr)`                | `stdDevPop(expression)`          | Population standard deviation.                       |
| `acc.stdDevSamp(expr)`               | `stdDevSamp(expression)`         | Sample standard deviation.                           |

The examples below are the common accumulators, all executed against a live database. The `N`-variants (`firstN` shown), `top`/`bottom`, and the standard-deviation accumulators share the same call shapes; `firstN` is exercised here as a representative of the family.

#### Examples [#examples-15]

##### count() and sum() [#count-and-sum]

```typescript
import { acc, fn } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .group((f) => ({ _id: null, total: acc.count(), durationSum: acc.sum(fn.literal(1)) }))
  .build();
const [{ total, durationSum }] = await db.execute(plan);
// { total: 2, durationSum: 2 }
```

##### avg(), min(), and max() [#avg-min-and-max]

```typescript
import { acc, fn } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .group((f) => ({
    _id: null,
    earliest: acc.min(f.createdAt),
    latest: acc.max(f.createdAt),
    avgYear: acc.avg(fn.year(f.createdAt)),
  }))
  .build();
const [stats] = await db.execute(plan);
// { earliest: <Date>, latest: <Date>, avgYear: 2024 }
```

##### first() and last() [#first-and-last]

```typescript
import { acc } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .sort({ createdAt: 1 })
  .group((f) => ({ _id: null, firstTitle: acc.first(f.title), lastTitle: acc.last(f.title) }))
  .build();
const [{ firstTitle, lastTitle }] = await db.execute(plan);
// { firstTitle: 'Hello world', lastTitle: 'Tutorial one' }
```

##### push() and addToSet() [#push-and-addtoset]

```typescript
import { acc } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .group((f) => ({
    _id: null,
    allTitles: acc.push(f.title),
    distinctKinds: acc.addToSet(f.kind),
  }))
  .build();
const [{ allTitles, distinctKinds }] = await db.execute(plan);
```

##### firstN() (an N-variant) [#firstn-an-n-variant]

```typescript
import { acc, fn } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .sort({ createdAt: 1 })
  .group((f) => ({ _id: null, firstTwo: acc.firstN({ input: f.title, n: fn.literal(2) }) }))
  .build();
const [{ firstTwo }] = await db.execute(plan);
// firstTwo is ['Hello world', 'Tutorial one']
```

## Expression helpers [#expression-helpers]

Expression helpers (`fn.*`) build the computed values used inside stages like `addFields()`, `project()`, `group()` accumulators, and `match()` (via `expr()`). Import them from `@prisma-next/mongo-query-builder`:

```typescript
import { fn } from '@prisma-next/mongo-query-builder';
```

The `fn.*` namespace mirrors MongoDB's aggregation expression operators: each helper is the camelCase name of the operator without its `$` prefix (`$toUpper` becomes `fn.toUpper`, `$dateToString` becomes `fn.dateToString`). The helpers below are grouped by category, with the ones exercised by the test suite; the full set follows the same naming pattern.

| Category     | Helpers (examples)                    | Notes                                                                        |
| ------------ | ------------------------------------- | ---------------------------------------------------------------------------- |
| Arithmetic   | `fn.add`, `fn.multiply`               | Numeric arithmetic over expressions.                                         |
| String       | `fn.concat`, `fn.toUpper`, `fn.split` | String composition and transformation.                                       |
| Date         | `fn.year`                             | Extract a component from a date field.                                       |
| Comparison   | `fn.eq`, `fn.gt`                      | Return a boolean expression; use `.node` where a raw expression is required. |
| Array        | `fn.size`, `fn.arrayElemAt`           | Array length and element access.                                             |
| Control flow | `fn.cond`                             | Ternary branch on a boolean expression.                                      |
| Literal      | `fn.literal`                          | Wrap a constant so it is not interpreted as a field path.                    |
| Type         | `fn.toObjectId`                       | Convert a value to an `ObjectId`.                                            |

#### Remarks [#remarks-15]

* **`fn.cond()`'s condition is a raw expression, not a filter.** Pass a comparison helper's `.node`: `fn.cond(fn.eq(a, b).node, thenExpr, elseExpr)`. Passing the `expr(...)` form (which is for `match()` filters) throws `TypeError: visitor.expr is not a function`.
* The `expr()` wrapper is only for `match()` filters. Inside `match()`, `expr(fn.gt(...))` is correct; inside `fn.cond()` it is not.
* **`fn.toObjectId()` cannot be composed over `fn.literal()`.** `fn.toObjectId(fn.literal(id))` throws (`Cannot read properties of undefined (reading 'codecId')`), because `fn.literal()` carries no field codec. Give `fn.toObjectId()` a real field-accessor expression instead.

#### Examples [#examples-16]

##### Arithmetic in a projection [#arithmetic-in-a-projection]

```typescript
import { fn } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .project((f) => ({
    title: 1,
    computed: fn.add(fn.literal(1), fn.multiply(fn.literal(2), fn.literal(3))),
  }))
  .build();
const rows = await db.execute(plan);
// computed is 7 for every document
```

##### String composition [#string-composition]

```typescript
import { fn } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .project((f) => ({ shout: fn.concat(fn.toUpper(f.title), fn.literal('!')) }))
  .build();
const rows = await db.execute(plan);
```

##### Branch with cond() [#branch-with-cond]

```typescript
import { fn } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .project((f) => ({
    title: 1,
    label: fn.cond(
      fn.eq(f.kind, fn.literal('tutorial')).node,
      fn.literal('is-tutorial'),
      fn.literal('not-tutorial'),
    ),
  }))
  .build();
const rows = await db.execute(plan);
```

##### Array length [#array-length]

```typescript
import { fn } from '@prisma-next/mongo-query-builder';

const plan = db.query
  .from('posts')
  .project((f) => ({ title: 1, titleWords: fn.size(fn.split(f.title, fn.literal(' '))) }))
  .build();
const rows = await db.execute(plan);
```

### pipe() [#pipe]

Append an arbitrary raw pipeline stage the typed methods don't cover.

#### Remarks [#remarks-16]

* Documented from the builder's source (`builder.ts`); **not exercised by the validation harness**.
* `pipe(stage)` appends a raw `MongoPipelineStage` node and collapses the row shape to an opaque document shape. `pipe<NewShape>(stage)` lets you declare the resulting shape yourself.
* Prefer a typed stage when one exists; `pipe()` is the in-chain escape hatch, and [`rawCommand()`](#rawcommand) is the whole-command one.

## Read terminals [#read-terminals]

A read terminal turns the chain into an executable plan. Pass the plan to `db.execute(plan)` to run it.

### build() and aggregate() [#build-and-aggregate]

Compile the pipeline into a plan.

#### Remarks [#remarks-17]

* `build()` produces the plan. `aggregate()` is an alias: it returns the identical plan and executes identically.
* Neither runs the query. Pass the plan to `db.execute(plan)` (or `runtime.execute(plan)`) to fetch documents.

#### Return type [#return-type-15]

| Return type | Example                          | Description                                |
| ----------- | -------------------------------- | ------------------------------------------ |
| Query plan  | `db.query.from('posts').build()` | A plan you execute with `db.execute(...)`. |

#### Examples [#examples-17]

##### build() and its aggregate() alias [#build-and-its-aggregate-alias]

```typescript
const built = db.query.from('posts').sort({ createdAt: 1 }).build();
const aggregated = db.query.from('posts').sort({ createdAt: 1 }).aggregate();
// built and aggregated produce the same plan

const rows = await db.execute(aggregated);
```

## Write methods [#write-methods]

The pipeline builder can write, not just read. Write methods are available at three points: on the root collection, after a `match()` filter, and as pipeline write terminals.

Updater callbacks (the `(f) => [...]` argument to the update methods) must return an **array** of operations. This is a firm rule:

* A bare (non-array) operation throws at build time: `Error: Unreachable: items.length > 0 but first is undefined`. This is an internal consistency guard, not a friendly validation message, so returning `[f.bio.set('x')]` rather than `f.bio.set('x')` matters. (Some older internal material shows the bare form. That is incorrect.)
* An empty array throws: `Updater returned no operations. Return at least one update from the callback ...`.
* You cannot mix operator-form ops and pipeline-form (`f.stage.*`) ops in one updater; doing so throws `Cannot mix ...`. Each updater is one form or the other.

### Root-level writes [#root-level-writes]

`insertOne`, `insertMany`, `updateAll`, `deleteAll`, and `upsertOne` are available directly on `from(root)`, before any stage.

#### Remarks [#remarks-18]

* These return a plan; execute it with `db.execute(plan)`.
* Write results are the driver's own result envelopes (`{ insertedId }`, `{ insertedIds }`, `{ deletedCount }`, `{ modifiedCount }`, `{ upsertedId }`), not decoded documents.

#### Examples [#examples-18]

##### insertOne() and insertMany() [#insertone-and-insertmany]

```typescript
const onePlan = db.query.from('users').insertOne({
  name: 'Carol',
  email: 'carol@example.com',
  bio: null,
  role: 'author',
  address: null,
});
const [insertOneResult] = await db.execute(onePlan);
// { insertedId: <ObjectId> }

const manyPlan = db.query.from('users').insertMany([
  { name: 'Carol', email: 'carol@example.com', bio: null, role: 'author', address: null },
  { name: 'Dave', email: 'dave@example.com', bio: null, role: 'reader', address: null },
]);
const [insertManyResult] = await db.execute(manyPlan);
// { insertedIds: { '0': <ObjectId>, '1': <ObjectId> } }
```

##### updateAll() (array-of-ops updater) [#updateall-array-of-ops-updater]

```typescript
const plan = db.query.from('users').updateAll((f) => [f.bio.set('everyone now has bio')]);
await db.execute(plan);
```

##### deleteAll() [#deleteall]

```typescript
const plan = db.query.from('users').deleteAll();
const [result] = await db.execute(plan);
// { deletedCount: 2 }
```

##### upsertOne() on the root (filter, then updater) [#upsertone-on-the-root-filter-then-updater]

On the root collection, `upsertOne()` takes a filter callback and an updater callback:

```typescript
const plan = db.query.from('users').upsertOne(
  (f) => f.email.eq('erin@example.com'),
  (f) => [f.name.set('Erin'), f.email.set('erin@example.com'), f.bio.set(null)],
);
const [result] = await db.execute(plan);
// inserts when no document matches: result.upsertedId is defined
```

### Writes after match() [#writes-after-match]

After a `match()` filter, `updateMany`, `updateOne`, `deleteMany`, `deleteOne`, `upsertOne`, `findOneAndUpdate`, and `findOneAndDelete` write against the matched documents.

#### Remarks [#remarks-19]

* The `match()` filter supplies the write's filter; you do not repeat it.
* After `match()`, `upsertOne()` takes the updater callback only (the filter comes from `match()`).
* `updateOne` / `deleteOne` affect at most one matching document; `updateMany` / `deleteMany` affect all matches.

#### Examples [#examples-19]

##### updateMany() and updateOne() [#updatemany-and-updateone]

```typescript
const manyPlan = db.query
  .from('users')
  .match((f) => f.role.eq('author'))
  .updateMany((f) => [f.bio.set('matched-many')]);
await db.execute(manyPlan);

const onePlan = db.query
  .from('users')
  .match((f) => f.email.eq('alice@example.com'))
  .updateOne((f) => [f.bio.set('single-update')]);
const [result] = await db.execute(onePlan);
// { matchedCount: 1, modifiedCount: 1 }
```

##### deleteMany() and deleteOne() [#deletemany-and-deleteone]

```typescript
const plan = db.query
  .from('users')
  .match((f) => f.role.eq('author'))
  .deleteMany();
const [result] = await db.execute(plan);
// { deletedCount: 2 }
```

##### upsertOne() after match() (updater only) [#upsertone-after-match-updater-only]

```typescript
const plan = db.query
  .from('users')
  .match((f) => f.email.eq('alice@example.com'))
  .upsertOne((f) => [f.bio.set('upserted via match')]);
const [result] = await db.execute(plan);
// updates on a hit: result.modifiedCount is 1
```

##### findOneAndUpdate() and returnDocument [#findoneandupdate-and-returndocument]

`findOneAndUpdate()` returns the matched document. Its second argument controls which image you get back: `returnDocument: 'before'` returns the pre-update document, `'after'` returns the post-update document. The default is `'after'`.

```typescript
const afterPlan = db.query
  .from('users')
  .match((f) => f.email.eq('alice@example.com'))
  .findOneAndUpdate((f) => [f.bio.set('changed')], { returnDocument: 'after' });
const [afterDoc] = await db.execute(afterPlan);
// afterDoc.bio is 'changed'

const beforePlan = db.query
  .from('users')
  .match((f) => f.email.eq('alice@example.com'))
  .findOneAndUpdate((f) => [f.bio.set('changed')], { returnDocument: 'before' });
const [beforeDoc] = await db.execute(beforePlan);
// beforeDoc.bio is the pre-update value
```

> [!WARNING]
> The option is
> 
> `returnDocument`
> 
> , not
> 
> `returnNewDocument`
> 
> `returnNewDocument` is not a valid option. TypeScript rejects it (the suggested fix is `returnDocument`). If you force it past the type system, the unknown key is silently ignored and the default (`'after'`) applies, so a typo produces a wrong-but-plausible result rather than a loud error. Use `returnDocument: 'before' | 'after'`.

##### findOneAndDelete() [#findoneanddelete]

```typescript
const plan = db.query
  .from('users')
  .match((f) => f.email.eq('alice@example.com'))
  .findOneAndDelete();
const [deleted] = await db.execute(plan);
// deleted is the removed document
```

### Update operation forms [#update-operation-forms]

Inside an updater callback, each operation targets a field. There are two mutually exclusive forms.

#### Remarks [#remarks-20]

* **Operator form**: field-level operators through the field accessor, for example `f.bio.set(value)`. Per-field operators include `set`, `unset`, `rename`, `inc`, `mul`, `min`, `max`, `push`, `addToSet`, `pop`, `pull`, `pullAll`, `currentDate`, and `setOnInsert`.
* **Pipeline form**: aggregation-pipeline update stages through `f.stage.*`, for example `f.stage.set({ bio: f.name.node })` (which emits an `$addFields`-style stage). Pipeline-form ops let an update reference other fields of the same document.
* The two forms cannot be mixed in a single updater (see the write-methods intro). An updater is entirely operator-form or entirely pipeline-form.

#### Examples [#examples-20]

##### Operator form [#operator-form]

```typescript
const plan = db.query
  .from('users')
  .match((f) => f.role.eq('author'))
  .updateMany((f) => [f.bio.set('operator form')]);
await db.execute(plan);
```

##### Pipeline form (f.stage.*) [#pipeline-form-fstage]

```typescript
const plan = db.query
  .from('users')
  .match((f) => f.role.eq('author'))
  .updateMany((f) => [f.stage.set({ bio: f.name.node })]);
await db.execute(plan);
// each author's bio is set to that author's own name
```

### Pipeline write terminals: out() and merge() [#pipeline-write-terminals-out-and-merge]

`out()` and `merge()` write the pipeline's output into a collection (`$out` / `$merge`).

#### Remarks [#remarks-21]

* `out(collection)` materializes the pipeline output into a destination collection, replacing its contents.
* `merge({ into })` streams the pipeline output into a target collection, merging with existing documents.
* Both are terminals: they return a plan you execute with `db.execute(plan)`, and produce no rows of their own.

#### Examples [#examples-21]

##### Materialize with out() [#materialize-with-out]

```typescript
const plan = db.query.from('users').out('users_snapshot');
await db.execute(plan);
// the users_snapshot collection now holds the pipeline output
```

##### Merge with merge() [#merge-with-merge]

```typescript
const plan = db.query.from('users').merge({ into: 'users_archive' });
await db.execute(plan);
```

## rawCommand() [#rawcommand]

Run a raw MongoDB aggregate command through the pipeline builder, bypassing the typed AST.

#### Remarks [#remarks-22]

* `db.query.rawCommand(command)` packages a raw command (for example `new RawAggregateCommand(collection, pipeline)`) into a plan. The rows come back as `unknown`; you type them yourself.
* Because it bypasses the typed AST, `rawCommand()` is the escape hatch for anything the typed builder can't express, including `_id` equality filters (see the [`match()`](#match) warning) and `$redact` with `$$KEEP` / `$$PRUNE`.
* For the full raw MongoDB surface (raw collection methods, untyped writes, and the undecoded-results behavior), see [Raw queries](https://www.prisma.io/docs/orm/next/reference/raw-queries).

#### Examples [#examples-22]

##### Run a raw aggregate pipeline [#run-a-raw-aggregate-pipeline]

```typescript
import { RawAggregateCommand } from '@prisma-next/mongo-query-ast/execution';

const plan = db.query.rawCommand(new RawAggregateCommand('posts', [{ $count: 'total' }]));
const rows = await db.execute(plan);
// [{ total: 2 }]
```

##### Filter by _id (the working escape hatch) [#filter-by-_id-the-working-escape-hatch]

```typescript
import { RawAggregateCommand } from '@prisma-next/mongo-query-ast/execution';
import { ObjectId } from 'mongodb';

const plan = db.query.rawCommand(
  new RawAggregateCommand('posts', [{ $match: { _id: new ObjectId(postId) } }]),
);
const rows = await db.execute(plan);
// a real ObjectId in a raw pipeline document matches correctly
```

## Related pages

- [`ORM client reference`](https://www.prisma.io/docs/orm/next/reference/orm-client): Reference for the Prisma Next ORM client's query, mutation, filter, and aggregate methods.
- [`Raw queries reference`](https://www.prisma.io/docs/orm/next/reference/raw-queries): Reference for Prisma Next raw query escape hatches: PostgreSQL raw SQL and MongoDB raw commands.
- [`SQL query builder reference`](https://www.prisma.io/docs/orm/next/reference/sql-query-builder): Reference for the Prisma Next SQL query builder's select, mutation, and grouped query methods.
- [`Transactions and runtime reference`](https://www.prisma.io/docs/orm/next/reference/transactions-and-runtime): Reference for the Prisma Next client lifecycle, transactions, prepared statements, and execution options.