Docs

Find and aggregate

Filters, projections, and a pipeline of stages over the pages of a collection.

find and the aggregation pipeline share one matcher. A leading $match runs while the pages are scanned. The pipeline is the BSON array a MongoDB shell already sends. There is no second syntax.

Predicates

Equality is { job: "CLERK" }. These operators work on find and inside $match. Numbers compare across int and double. Dates compare by instant.

Operator Meaning

$eq $ne Equal, or not equal.

$gt $gte $lt $lte Ordered comparison.

$in $nin Value is in a list, or outside it.

$exists The field is present, or absent.

$and $or $nor Combine predicates.

$not Negate one operator document.

$regex Match a string field.

$type Match the BSON type of a field.

$elemMatch One array element satisfies every predicate in the subdocument.

Operator	Meaning
`$eq` `$ne`	Equal, or not equal.
`$gt` `$gte` `$lt` `$lte`	Ordered comparison.
`$in` `$nin`	Value is in a list, or outside it.
`$exists`	The field is present, or absent.
`$and` `$or` `$nor`	Combine predicates.
`$not`	Negate one operator document.
`$regex`	Match a string field.
`$type`	Match the BSON type of a field.
`$elemMatch`	One array element satisfies every predicate in the subdocument.

db.emp.find({
  sal: { $gte: 1000, $lt: 2000 },
  job: { $in: ["CLERK", "SALESMAN"] }
})

db.emp.find({ ename: { $regex: "^S" } })

Shape the rows

Projection, sort, skip, and limit work on find and as pipeline stages. Include fields, or exclude them. Mixing include and exclude in one projection is limited the way MongoDB limits it: _id is the field you may turn off while including others.

db.emp.aggregate([
  { $match: { deptno: 20 } },
  { $project: { ename: 1, sal: 1, _id: 0 } },
  { $sort: { sal: -1 } },
  { $skip: 1 },
  { $limit: 3 }
])

$addFields keeps the document and adds fields. $replaceRoot makes a subdocument the new root. $unwind emits one document per array element. $count emits one document with the number of rows that reached that stage.

Group

$group buckets documents by _id. Accumulators:

Accumulator Result

$sum $avg Total, or mean, of a numeric expression.

$min $max Smallest or largest value.

$count How many documents landed in the bucket. Write { $sum: 1 }.

$first $last The first or last value seen in the bucket.

$push $addToSet An array of values. $addToSet keeps each value once.

Accumulator	Result
`$sum` `$avg`	Total, or mean, of a numeric expression.
`$min` `$max`	Smallest or largest value.
`$count`	How many documents landed in the bucket. Write `{ $sum: 1 }`.
`$first` `$last`	The first or last value seen in the bucket.
`$push` `$addToSet`	An array of values. `$addToSet` keeps each value once.

db.emp.aggregate([
  { $group: {
      _id: "$job",
      headcount: { $sum: 1 },
      payroll: { $sum: "$sal" }
  }},
  { $sort: { payroll: -1 } }
])

The same question in SQL is SELECT job, COUNT(*), SUM(sal) FROM emp GROUP BY job. The rows match. SQL is one statement. The pipeline is how you add a lookup or write the result out.

Look up another collection

$lookup reads a second collection through the buffer pool. Equality uses localField and foreignField. The two collections are not copied into one big list first.

db.emp.aggregate([
  { $match: { ename: "SMITH" } },
  { $lookup: {
      from: "dept",
      localField: "deptno",
      foreignField: "deptno",
      as: "department"
  }}
])

let plus a nested pipeline runs that pipeline for each local document. A $match inside it accepts $expr.

On a shard router, a stage other than $match, $project, or $unwind is refused. A $group inside each shard is not the group of the whole collection. Run the group against one shard, or against a process that holds the collection.

Write the result

$out inserts the pipeline result into a collection. That write goes through the log, like any other insert.

db.emp.aggregate([
  { $match: { sal: { $gt: 2000 } } },
  { $project: { ename: 1, sal: 1 } },
  { $out: "high_earners" }
])

Inside a transaction, $out is refused. The transaction page explains what a transaction can see.

An empty pipeline

aggregate with an empty pipeline returns the collection. An unknown stage or operator fails the command and names what it rejected.