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 finish the chain with the call that builds the query. It is the MongoDB counterpart to the SQL query builder: a lower-level API for the queries the ORM client doesn't express.

Everything the builder produces becomes a MongoDB aggregation pipeline. There is no find() or distinct() here. Use db.orm or rawCommand() for those. To fetch a single document, filter and then limit(1). Use the ORM client (db.orm) for everyday reads and writes across models and relations. Reach for the pipeline builder when you need to group documents, to join another collection and keep transforming the result, or to write the result into a collection ($out and $merge). This page documents every stage, accumulator, expression helper, and write method.

Example schema

The examples run against the schema below, the same MongoDB schema as the ORM client page. Article and Tutorial are two kinds of Post, stored in one posts collection and told apart by the value of the kind field. @@base(Post, "article") and @@discriminator(kind) set that up. See @@base on the ORM client page. @@type("mongo/string@1") says the enum is stored as a MongoDB string, and @1 is the version of that type id, always @1 today. None of that matters to a pipeline: kind is an ordinary field you can filter and group on.

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

Every pipeline starts with db.query.from('posts'), naming the collection to aggregate over. It ends with a call that turns the chain into a query you can run. Between them you chain stages.

db is the client you create with mongo(...), whose dbName option is the name of the MongoDB database to use. See Transactions and runtime for every option and for how to create the client. Three Prisma ORM import paths appear on this page: @prisma/orm-mongo/runtime holds the mongo() client, @prisma/orm-mongo/query-builder holds mongoQuery, fn, acc, and expr, and @prisma/orm-mongo/query-ast/execution holds the Mongo*Stage classes, MongoAggFieldRef, and MongoFieldFilter. Every example holds the built query in a variable named plan.

Prisma ORM 8 renames schema.prisma to contract.prisma. Run npx prisma contract emit before any code on this page compiles: it writes contract.json, used at run time, and contract.d.ts, used for types. The example below imports contract.json with with { type: 'json' }, which works with the TypeScript settings prisma orm init writes for you. Coming from Prisma ORM 7 has the setup steps.

from()

Enter the pipeline builder on a collection.

Remarks

• from() takes the collection name, the same name you use on db.orm ('posts', 'users'), not the model name Post. The collection name is the model's @@map value, or the model name with a lowercase first letter when there is no @@map.
• Passing an unknown collection name throws right away, with an error whose code is ORM.MODEL_UNKNOWN and whose message is Unknown root: "<name>". Valid roots: ... (the message says root; it means the collection name).
• from() returns a builder you chain stages onto. Call insertOne() and insertMany() on from() directly, before any stage.
• You can build the same pipelines without a client. import { mongoQuery } from '@prisma/orm-mongo/query-builder', then mongoQuery({ contractJson }).from('posts'). Import contractJson the same way the example below does. mongoQuery only builds queries. It never runs one, so you still need a client's runtime to get results. Use it in tests, or to build a query once and run it through any client you like.

Options

Return type

Examples

Enter the builder on a collection

import mongo from '@prisma/orm-mongo/runtime';
import type { Contract } from './contract.d';
import contractJson from './contract.json' with { type: 'json' };

const db = mongo<Contract>({ contractJson, url: process.env.MONGODB_URL, dbName: 'app' });
const runtime = await db.runtime();

const plan = db.query.from('posts').build();
const posts = await runtime.query(plan);

Building and executing a pipeline

build() returns the built query, which the examples hold in a variable named plan. Nothing runs until you pass it to runtime.query(...). Every later example reuses the db and runtime from the example above.

Remarks

• On MongoDB, db.runtime() returns a promise, so you must await it: const runtime = await db.runtime(). Hold it in a variable and reuse it.
• await that call for an array of documents, or write for await (const doc of runtime.query(plan)) { ... } to take one document at a time.
• There is no db.execute on the MongoDB client.
• Read results are decoded. If every stage in your pipeline is one of match(), sort(), limit(), skip(), sample(), project(), addFields(), and vectorSearch(), _id comes back as a hex string. If any stage is not in that list, including lookup() and group(), every document comes back raw, with each value as MongoDB stores it. An _id that MongoDB generated is then an ObjectId object from the mongodb package, the MongoDB driver Prisma ORM uses. Compare those ids as strings, as lookup() shows. A DateTime field comes back as a JavaScript Date either way.

Examples

Execute through the client

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

Pipeline stages

Stages transform the documents flowing through the pipeline. Chain them in order; each stage's output feeds the next. The (f) => ... callbacks reference the current document's fields, one property per field.

match()

Filter documents by a predicate.

Remarks

• match() takes a callback whose f argument has one property per field, and returns a filter expression ((f) => f.kind.eq('tutorial')).
• Scalar fields expose these filter operators: eq, ne, gt, gte, lt, lte, in, nin, exists, and type. They work like the MongoDB query operators of the same name ($ne, $gt, $in, $exists, $type, and so on).
• To compare two computed values rather than a field against a constant, wrap the comparison in expr(...): match((f) => expr(fn.gt(fn.year(f.createdAt), fn.literal(2023)))). fn holds the helpers that build expressions, one per MongoDB operator. expr() wraps one of those values so match() accepts it. The fn.* helpers take expressions, not plain values, so wrap every constant in fn.literal(). See Expression helpers.

Where each form goes. .node is the raw MongoDB expression inside an fn.* value:

Options

Return type

Examples

Filter by a field

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

Aggregation-expression predicate

import { fn, expr } from '@prisma/orm-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 runtime.query(plan);

const postId = '6650f1c2a1b2c3d4e5f60003';
const post = await db.orm.posts.where({ _id: postId }).first();

sort()

Order documents by a field spec.

Remarks

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

Options

Return type

Examples

Sort descending

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

limit()

Cap the number of documents.

Remarks

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

Options

Return type

Examples

Fetch a single document

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

skip()

Offset into the sorted result set.

Options

Return type

Examples

Paginate

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

sample()

Draw a random subset of documents.

Options

Return type

Examples

Random subset

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

addFields()

Compute new fields and attach them to each document.

Remarks

• addFields() takes a callback returning an object of new field names to computed expression-helper values. Existing fields are preserved.

Options

Return type

Examples

Attach a computed field

import { fn } from '@prisma/orm-mongo/query-builder';

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

lookup()

Join documents from another collection ($lookup).

Remarks

• lookup() takes a callback that builds the join with from(collection).on((local, foreign) => ({ local, foreign })).as(name): the other collection, the two fields to match on, and the name of the output array field.
• The object returned by on() has two fixed keys, local and foreign. Write ({ local: local.authorId, foreign: foreign._id }).
• The joined documents arrive in an array under the as name. When nothing matches, that array is empty.
• A joined document's _id and the _id of the document it was joined to are both ObjectId objects, and two ObjectId objects are never === even when they hold the same id. Compare them as strings: String(post.author[0]._id) === String(post.authorId).

Options

Return type

Examples

Join a foreign collection

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 runtime.query(plan);

project()

Reshape each document.

Remarks

• 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) }))). Write 1 to keep a field, or an expression to compute it. There is no 0 form here: the callback form cannot drop a field.

Options

Return type

Examples

Key-list form

const plan = db.query.from('posts').project('title', 'kind').build();
const trimmed = await runtime.query(plan);

Callback form

import { fn } from '@prisma/orm-mongo/query-builder';

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

unwind()

Unroll an array field into one document per element.

Remarks

• unwind(field, { preserveNullAndEmptyArrays? }) maps to MongoDB's $unwind. Name the field holding the array. TypeScript accepts any field of the document; nothing checks that it holds an array.

Options

Return type

Examples

Unroll an array field

This example needs a tags String[] field on Post, which the example schema does not have.

const plan = db.query
  .from('posts')
  .match((f) => f.title.eq('Hello world'))
  .unwind('tags')
  .sort({ tags: 1 })
  .build();
const perTag = await runtime.query(plan);

group()

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

Remarks

• group() takes a callback that receives only the f argument, one property per field ((f) => ...), and returns a spec object. The spec's _id sets the grouping key; every other key must be an accumulator.
• acc holds the accumulators group() accepts, such as a count or a sum, documented under Accumulators. Import it and use it inside the callback, not as a second callback argument: import { acc } from '@prisma/orm-mongo/query-builder', then acc.count(), acc.push(f.title). The callback takes one argument; there is no (f, acc) => ... form.
• A _id: null key groups the whole collection into a single bucket.
• A non-accumulator value for a non-_id key throws as soon as you call group(), with an error whose code is ORM.ARGUMENT_INVALID: group() field "<name>" must use an accumulator (e.g. acc.sum(), acc.count()). Got "<kind>" expression. A null value for a key other than _id throws with the same code and the message group() field "<name>" must not be null. Only _id can be null.

Options

Return type

Examples

Group by a field

import { acc } from '@prisma/orm-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 runtime.query(plan);

Group the whole collection

import { acc } from '@prisma/orm-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 runtime.query(plan);

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

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

replaceRoot()

Promote a computed sub-document to the top level.

Remarks

• replaceRoot() takes a callback returning an expression that evaluates to an object. 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

Return type

Examples

Promote a looked-up document

import { fn } from '@prisma/orm-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 runtime.query(plan);

count()

Reduce the pipeline to a single document holding the count. It is not acc.count(), which counts the documents in one group inside group().

Options

Return type

Examples

Count documents

const plan = db.query.from('posts').count('total').build();
const [{ total }] = await runtime.query(plan);

sortByCount()

Group by an expression and sort descending by group size.

Remarks

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

Options

Return type

Examples

Count occurrences of a field value

const plan = db.query
  .from('posts')
  .sortByCount((f) => f.kind)
  .build();
const byKind = await runtime.query(plan);

redact()

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

Remarks

• redact() maps to MongoDB's $redact, whose expression must evaluate to one of MongoDB's $$ variables: $$KEEP, $$DESCEND, or $$PRUNE.
• Write those variables with f.rawPath('$KEEP') and f.rawPath('$PRUNE'). f.rawPath(path) names a field or a MongoDB $$ variable by its raw string, with no checking against your model, and it prefixes $ to the string you give it. There is no expression helper for a $$ variable, and fn.literal(...) cannot stand in: the server rejects both fn.literal('KEEP') and fn.literal('$$KEEP').
• For which fn.* values need their .node property here, see the table under match(). For anything more involved, use rawCommand(), where you write the $redact stage with $$KEEP and $$PRUNE directly.

Options

Return type

Examples

Keep tutorials and prune everything else

import { fn } from '@prisma/orm-mongo/query-builder';

const plan = db.query
  .from('posts')
  .redact((f) =>
    fn.cond(fn.eq(f.kind, fn.literal('tutorial')).node, f.rawPath('$KEEP'), f.rawPath('$PRUNE')),
  )
  .build();
const tutorials = await runtime.query(plan);

Option-object stages

Several stages take a single MongoDB stage options object rather than a typed (f) => ... callback. Each one carries the same meaning it has in MongoDB. Where an option below is called a raw expression, it wants a MongoDB expression object rather than a typed builder value: build one with the fn.* helpers and pass its .node property, or name a field with MongoAggFieldRef.of('duration'), which is already a raw expression and takes no .node.

Remarks

• bucket({ groupBy, boundaries, default_?, output? }) sorts documents into the ranges you name. default is a reserved word in JavaScript, so the key is default_. groupBy is a raw expression.
• bucketAuto({ groupBy, buckets, output?, granularity? }) sorts them into a given number of ranges, chosen so each holds about the same number of documents. groupBy is a raw expression. granularity is a string that MongoDB reads as the series it rounds the bucket boundaries to. Prisma ORM passes it through unchanged, accepts any string, and MongoDB rejects the ones it does not know.
• geoNear({ near, distanceField, spherical?, maxDistance?, minDistance?, query?, key?, distanceMultiplier?, includeLocs? }) sorts documents by their distance from a point and writes that distance into distanceField. near is the point to measure from, and Prisma ORM passes it through to MongoDB unchanged.
• graphLookup({ from, startWith, connectFromField, connectToField, as, maxDepth?, depthField?, restrictSearchWithMatch? }) follows links from document to document through one collection and collects what it reaches into as. startWith is a raw expression.
• setWindowFields({ partitionBy?, sortBy?, output }) computes a value for each document from the documents around it, such as a running total. partitionBy is a raw expression. output is an object mapping each new field name to { operator, window? }, where operator is a raw expression, usually an accumulator: { runningTotal: { operator: MongoAggAccumulator.sum(MongoAggFieldRef.of('duration')), window: { documents: ['unbounded', 0] } } }.
• densify({ field, partitionByFields?, range }) adds documents to fill the gaps in a sequence of numbers or dates. range is { step, unit?, bounds }, where bounds is 'full', 'partition', or a pair of start and end values.
• fill({ partitionBy?, partitionByFields?, sortBy?, output }) replaces missing or null values with a value you choose. partitionBy is a raw expression. output is an object mapping each field name to { method } or { value }, where value is an expression and method is a string that Prisma ORM passes through to MongoDB unchanged.
• facet(facets) takes an object mapping each output field name to an array of stages.
• unionWith(collection, pipeline?) takes a collection name and an optional array of stages.
• The stages you pass to facet and unionWith are classes, one class per MongoDB stage, named Mongo<Stage>Stage. Import them, along with MongoAggFieldRef and MongoAggAccumulator, from @prisma/orm-mongo/query-ast/execution. Each class takes the same arguments as the MongoDB stage it builds. The three used below are MongoCountStage(field), MongoSortStage(spec), and MongoLimitStage(n).

Examples

Concatenate another collection with unionWith()

const plan = db.query.from('posts').unionWith('users').build();
const combined = await runtime.query(plan);

Run several sub-pipelines with facet()

MongoFieldFilter is the filter class from @prisma/orm-mongo/query-ast/execution. See MongoFieldFilter on the ORM client page for its methods, such as gt and in.

import { MongoCountStage, MongoFieldFilter, MongoLimitStage, MongoMatchStage, MongoSortStage } from '@prisma/orm-mongo/query-ast/execution';

const plan = db.query
  .from('posts')
  .facet({
    totalCount: [new MongoCountStage('count')],
    newest: [new MongoSortStage({ createdAt: -1 }), new MongoLimitStage(2)],
    tutorials: [
      new MongoMatchStage(MongoFieldFilter.eq('kind', 'tutorial')),
      new MongoCountStage('count'),
    ],
  })
  .build();
const [facets] = await runtime.query(plan);
// facets.totalCount is [{ count: <number of posts> }]; facets.newest is the two newest posts

Sort into ranges with bucket()

MongoAggFieldRef.of() takes any field path as a string and does not check it against your models. duration is a field on Tutorial, so posts without it fall into the Other bucket.

import { MongoAggFieldRef } from '@prisma/orm-mongo/query-ast/execution';

const plan = db.query
  .from('posts')
  .bucket({
    groupBy: MongoAggFieldRef.of('duration'),
    boundaries: [0, 15, 60],
    default_: 'Other',
  })
  .build();
const buckets = await runtime.query(plan);
// one document per bucket, each { _id: <lower boundary>, count: n }

Atlas-only stages

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

Remarks

• These stages need MongoDB Atlas. Nothing stops you from building them against a database that is not on Atlas. The error only appears when you run the query.
• search(config, index?) takes an Atlas Search operator object, for example { text: { query: 'hello', path: 'title' } }, and an optional index, which names the Atlas Search index to use.
• searchMeta(config, index?) takes the same two arguments and returns counts and facets instead of documents.
• vectorSearch({ index, path, queryVector, numCandidates, limit, filter? }) takes the index name, the field holding the vectors, the vector to search for as an array of numbers, how many candidates to consider, how many documents to return, and an optional filter object.

Return type

Accumulators

Accumulators compute per-group values inside a group() stage. _id: null in the examples below means one group holding every document. Import them from @prisma/orm-mongo/query-builder:

import { acc } from '@prisma/orm-mongo/query-builder';

The builder exposes nineteen accumulators:

• firstN, lastN, maxN, and minN take { input, n }. input is the expression whose values you collect.
• top and bottom take { output, sortBy }. topN and bottomN take the same two plus n.
• output is the expression whose value you want back from each chosen document. sortBy is a plain object such as { createdAt: 1 }, where 1 sorts ascending and -1 sorts descending. Its keys are plain strings because they name fields, not values.
• Wherever a signature takes n, wrap the number: write n: fn.literal(2), not n: 2. fn.literal comes from the expression helpers below.

Put a sort() before the group() when you use acc.first() or acc.last(). Without one, the document MongoDB calls first is arbitrary and can change between runs.

Examples

count() and sum()

acc.sum(fn.literal(1)) adds 1 for each document, which counts them. To add up a field instead, pass the field, as in acc.sum(f.views) on a collection with a numeric views field. The example schema's posts has no such field, so that call needs a collection of your own.

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

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

avg(), min(), and max()

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

first() and last()

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 runtime.query(plan);
// { firstTitle: 'Hello world', lastTitle: 'Tutorial one' }

push() and addToSet()

Both take one expression: group((f) => ({ _id: null, allTitles: acc.push(f.title), distinctKinds: acc.addToSet(f.kind) })). allTitles holds one entry per document. distinctKinds holds each value once.

firstN() (an N-variant)

const plan = db.query.from('posts').sort({ createdAt: 1 })
  .group((f) => ({
    _id: null,
    firstTwo: acc.firstN({ input: f.title, n: fn.literal(2) }),
    newestTitle: acc.top({ output: f.title, sortBy: { createdAt: -1 } }), // the title string, not a document
  }))
  .build();
const [{ firstTwo, newestTitle }] = await runtime.query(plan);
// firstTwo is ['Hello world', 'Tutorial one']

Expression helpers

Expression helpers (fn.*) build the computed values used inside stages like addFields(), project(), group() accumulators, and match() (via expr()). Inside a stage, write f.title or fn.toUpper(...), never 'hello' or 3 on their own. Wrap a constant in fn.literal('hello'). The update operators on the write methods and the MongoFieldFilter methods work the other way round. They take ordinary JavaScript values, so write f.bio.set('hello'), not f.bio.set(fn.literal('hello')). Import the helpers from @prisma/orm-mongo/query-builder:

import { fn } from '@prisma/orm-mongo/query-builder';

Each helper is the camelCase name of the MongoDB aggregation operator without its $ prefix, so $toUpper is fn.toUpper and $dateToString is fn.dateToString. Five helpers are named differently:

The first three are renamed because in and typeof are reserved words in JavaScript and toString is already a method on every JavaScript object.

Here is every helper. Anything missing from this list has no helper, including $and, $or, $not, $switch, $ifNull, $map, $reduce, and $filter.

fn.literal(value) wraps a constant so MongoDB reads it as a value and not as the name of a field. .node is the raw MongoDB expression inside an fn.* value, and a field reference such as f.title has one too. Passing an expr(...) value where .node is expected throws a TypeError. This table is the whole rule for when to write .node:

expr() is what lets a match() filter compare computed values. Import it beside fn:

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

const plan = db.query.from('posts').match((f) => expr(fn.gt(fn.year(f.createdAt), fn.literal(2024)))).build();

For OR, AND, or NOT, pass match() a filter you build yourself instead of a callback:

import { MongoAndExpr, MongoFieldFilter, MongoOrExpr } from '@prisma/orm-mongo/query-ast/execution';

const kindFilter = MongoOrExpr.of([
  MongoFieldFilter.eq('kind', 'tutorial'),
  MongoFieldFilter.eq('kind', 'guide'),
]);
const plan = db.query.from('posts').match(kindFilter).build();
const rows = await runtime.query(plan);
// rows are the documents whose kind is either tutorial or guide

Remarks

• There is no fn.* helper for OR, AND, or NOT. In a match() filter, build the filter yourself: MongoOrExpr.of([...]) is OR, MongoAndExpr.of([...]) is AND, and .not() on any filter is NOT. The example above shows OR.
• Each entry in those lists is a MongoFieldFilter, which has eq, neq, gt, gte, lt, lte, in, nin, isNull, and isNotNull. These are the same filter methods the ORM client uses, described under Combinators. A filter you build yourself names the field as a string, as in MongoFieldFilter.eq('kind', 'tutorial'), and nothing checks that string against your model.
• "Not equal" has two spellings on this page. The filter method is MongoFieldFilter.neq and the expression helper is fn.ne.
• Inside a computed value in project() or addFields() there is no way to write OR, AND, or NOT. Write that stage yourself with pipe(), or write the whole command with rawCommand().
• fn.slice(array, ...rest) and fn.range(start, end, step) take positional arguments, and so does every helper not listed in the table below. This page does not give the positional order for those helpers. Look each one up in MongoDB's aggregation operator reference. Each argument is an expression from the f argument or a fn.literal(...) value.
• fn.toObjectId() cannot take a fn.literal() value. fn.toObjectId(fn.literal(id)) throws a TypeError. There is no way to turn a string id into an ObjectId inside a pipeline. Build the ObjectId in your own code instead and put it in a raw $match with rawCommand(), as the last example on this page does.

These helpers take one object of named arguments instead of positional ones:

In fn.convert, to takes any MongoDB type name written as an expression, such as fn.literal('int') or fn.literal('objectId').

Examples

Arithmetic in a projection

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 runtime.query(plan);
// computed is 7 for every document

String composition

project((f) => ({ shout: fn.concat(fn.toUpper(f.title), fn.literal('!')) })) puts the title in upper case next to a !.

Branch with cond()

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 runtime.query(plan);

pipe()

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

Remarks

• pipe(stage) appends a raw stage that you construct yourself, and keeps the row type you already had. The stages are the Mongo*Stage classes, one per MongoDB stage, from @prisma/orm-mongo/query-ast/execution. The class name is Mongo, then the stage name without its $, then Stage, so $unwind is MongoUnwindStage. The example below uses MongoMatchStage and MongoCountStage. The same module exports MongoFieldFilter, which builds the filter a MongoMatchStage takes.
• pipe<NewShape>(stage) declares the row type the rows have after that stage. NewShape is a type you write yourself, as at the end of the example below.
• After a pipe() stage the TypeScript type no longer describes the rows. They come back exactly as MongoDB returns them, so a date is a Date and an _id that MongoDB generated is an ObjectId from the mongodb package, the driver Prisma ORM runs your queries through.
• Prefer a typed stage where one exists. pipe() adds one raw stage to a typed chain, and rawCommand() replaces the whole command.

Examples

Append a raw stage

import { MongoCountStage, MongoFieldFilter, MongoMatchStage } from '@prisma/orm-mongo/query-ast/execution';

const plan = db.query.from('posts')
  .pipe(new MongoMatchStage(MongoFieldFilter.eq('kind', 'tutorial'))).build();
const rows = await runtime.query(plan);

type Counted = { total: number };
const countPlan = db.query.from('posts').pipe<Counted>(new MongoCountStage('total')).build();

Read methods

Finish a read chain with build(), then run the built query with runtime.query(...).

build() and aggregate()

Compile the pipeline into a query you can run.

Remarks

• Write build(). aggregate() is another name for the same method. Neither runs the query: pass the result to runtime.query(...) to fetch documents.

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 at the end of any pipeline, where out() and merge() write the output into a collection. A write method returns the built query on its own, so there is no build() call after one. The examples below name that built query plan.

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

• Always return an array. Returning a bare operation, f.bio.set('x') rather than [f.bio.set('x')], throws as soon as you call the write method.
• An empty array throws an error whose code is ORM.MUTATION_DATA_MISSING: Updater returned no operations. Return at least one update from the callback ....
• An updater is either all operator form or all pipeline form. Operator form calls an operator on a field, as in f.bio.set(value). Pipeline form can read other fields of the same document, and it calls f.stage.*. Type f.stage exactly: stage is not a placeholder for a stage name. You cannot mix the two in one updater. TypeScript rejects the mixed array, and at run time it throws an error whose code is ORM.ARGUMENT_INVALID: Cannot mix ....
• Applying the same operator to the same field twice in one updater throws an error whose code is ORM.ARGUMENT_INVALID: Update spec collision: ....

Root-level writes

insertOne, insertMany, updateAll, deleteAll, and upsertOne are available on the collection you name in from(...), before you add any stage.

Remarks

• These return a built query. Run it with runtime.query(...).
• Write results are result objects, not documents: insertOne gives { insertedId }, insertMany gives { insertedIds, insertedCount }, the update methods and upsertOne give { matchedCount, modifiedCount }, plus upsertedCount and upsertedId when an upsert inserted a document, and the delete methods give { deletedCount }. A write returns one result object, as the single row of the result, which is why every example here reads it with const [result] = ....
• The document you pass to insertOne / insertMany is a plain record. TypeScript requires no particular field, and the builder does not check the record against your contract. You can leave nullable fields out. The examples below pass null for them to make the document shape explicit.
• You can leave _id out and let MongoDB assign one.
• On the way in, values are stored exactly as you pass them, so pass a Date for a date field and an ObjectId from the mongodb package for an id field. Nothing turns a string into either. This is the write direction only. Reads are decoded as the first half of this page describes.
• match() filter values follow the same rule as the values you insert. The one value you cannot filter on this way is _id. An _id filter written in match() never matches anything, so use rawCommand() instead. The match() warning gives the detail, and the last example on this page shows the form that works.
• insertMany([]) throws an error whose code is ORM.MUTATION_DATA_MISSING.

Examples

insertOne() and insertMany()

const onePlan = db.query.from('users')
  .insertOne({ name: 'Carol', email: 'carol@example.com', bio: null, role: 'author', address: null });
const [insertOneResult] = await runtime.query(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 runtime.query(manyPlan);
// { insertedIds: [<ObjectId>, <ObjectId>], insertedCount: 2 }

updateAll() (array-of-ops updater)

db.query.from('users').updateAll((f) => [f.bio.set('everyone now has bio')]) builds the update. Pass it to runtime.query(...) to run it.

deleteAll()

db.query.from('users').deleteAll() builds a delete for every document. Pass it to runtime.query(...) and read deletedCount from the single result object.

upsertOne() on the root (filter, then updater)

upsertOne(filterCallback, updaterCallback). The first callback returns the filter. The second returns the array of update operations.

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')]);
const [result] = await runtime.query(plan);
// inserts when no document matches: result.upsertedId is defined

Writes after match()

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

Remarks

• The match() filter supplies the write's filter, so 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. Which document that is, this builder gives you no way to choose.
• findOneAndUpdate() and findOneAndDelete() return the document exactly as the mongodb driver gives it, so its _id is an ObjectId from the mongodb package even though the TypeScript type says string. To get the id as a string, write String(result._id).
• findOneAndUpdate() and findOneAndDelete() are offered straight after match() and nowhere else. TypeScript withdraws them after every other stage, sort() and skip() included. If a cast gets one past TypeScript after a skip(), it throws an error whose code is ORM.OPERATION_UNSUPPORTED.

Examples

updateMany() and updateOne()

const manyPlan = db.query.from('users').match((f) => f.role.eq('author'))
  .updateMany((f) => [f.bio.set('matched-many')]);
await runtime.query(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 runtime.query(onePlan);
// { matchedCount: 1, modifiedCount: 1 }

deleteMany() and deleteOne()

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

upsertOne() after match() (updater only)

db.query.from('users').match((f) => f.email.eq('alice@example.com')).upsertOne((f) => [f.bio.set('upserted via match')]) builds the upsert. On a hit, result.modifiedCount is 1.

findOneAndUpdate() and returnDocument

findOneAndUpdate() returns the matched document. Its second argument is optional. That argument takes returnDocument, which controls which version you get back: 'before' returns the document before the update, 'after' returns it after. The default is 'after'. The same argument takes upsert, which defaults to false and inserts a document when nothing matches.

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

findOneAndDelete()

db.query.from('users').match((f) => f.email.eq('alice@example.com')).findOneAndDelete() builds the delete. Its single result row is the removed document.

Update operation forms

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

Remarks

• Operator form: call an operator on the f argument, one operator per field. There are fourteen, in the table below.
• Pipeline form: aggregation-pipeline update stages through f.stage.*, for example f.stage.set({ bio: f.name.node }). The four stages are set, unset, replaceRoot, and replaceWith.
• 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.

Operator form takes these arguments:

Examples

Operator form

const plan = db.query.from('users').match((f) => f.role.eq('author'))
  .updateMany((f) => [f.bio.set('operator form')]);
await runtime.query(plan);
// on a collection with a numeric views field and an array tags field (posts has neither):
// updateMany((f) => [f.views.inc(1), f.tags.push('mongodb')])

Pipeline form (f.stage.*)

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

Pipeline write methods: out() and merge()

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

Remarks

• out(collection) writes the pipeline output into a destination collection, replacing its contents. Pass a second argument, the database name as a string, to write into a different database: out('users_snapshot', 'archive').
• merge({ into }) writes the pipeline output into a target collection, merging with existing documents. into is a collection name or { db, coll }. The options object also takes on, which is one field name as a string or several as an array of strings, plus whenMatched and whenNotMatched.
• whenMatched takes 'replace', 'keepExisting', 'merge', or 'fail', or an array of update stages, which are MongoAddFieldsStage, MongoProjectStage, and MongoReplaceRootStage objects from @prisma/orm-mongo/query-ast/execution. MongoAddFieldsStage takes one object of field names and expressions, as in new MongoAddFieldsStage({ ... }). whenNotMatched takes 'insert', 'discard', or 'fail'. A misspelled value fails at the database rather than in TypeScript.
• Both end the chain. They return a built query you run with runtime.query(...), and that call returns an empty array, because the output goes to the destination collection and not to your program.

Examples

Write the output with out()

db.query.from('users').out('users_snapshot') builds the write. After you pass it to runtime.query(...), the users_snapshot collection holds the pipeline output.

Merge with merge()

const plan = db.query.from('users')
  .merge({ into: 'users_archive', on: 'email', whenMatched: 'merge', whenNotMatched: 'insert' });
await runtime.query(plan);

rawCommand()

Run a raw MongoDB aggregate command through the pipeline builder.

Remarks

• db.query.rawCommand(command) takes a command you build yourself, for example new RawAggregateCommand(collection, pipeline), and sends it as you wrote it. The rows come back as unknown, so you type them yourself.
• Use it for anything the typed builder cannot express, including _id equality filters (see the match() warning) and MongoDB's $redact stage.
• For the full raw MongoDB API (raw collection methods, untyped writes, and rows that come back as the driver gives them), see Raw queries.

Examples

Run a raw aggregate pipeline

import { RawAggregateCommand } from '@prisma/orm-mongo/query-ast/execution';

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

Filter by _id (the way that works)

import { ObjectId } from 'mongodb';

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