MongoDB aggregation cheat sheet

{ $match: { status: "active", age: { $gte: 18 } } }

{ $limit: 10 }

{ $skip: 20 }

{ $sample: { size: 5 } }

{ $geoNear: {
  near: { type: "Point", coordinates: [-73.99, 40.73] },
  distanceField: "dist",
  maxDistance: 5000,
  spherical: true
} }

{ $project: { name: 1, total: { $multiply: ["$price", "$qty"] } } }

{ $addFields: { totalPrice: { $multiply: ["$price", "$qty"] } } }

{ $unset: ["tempField", "internal.debug"] }

{ $replaceRoot: { newRoot: "$address" } }
// or equivalently:
{ $replaceWith: "$address" }

{ $unwind: "$tags" }
// with options:
{ $unwind: { path: "$tags", preserveNullAndEmptyArrays: true } }

{ $group: {
  _id: "$department",
  total: { $sum: "$salary" },
  count: { $sum: 1 },
  avgSalary: { $avg: "$salary" }
} }

{ $count: "totalDocuments" }

{ $bucket: {
  groupBy: "$price",
  boundaries: [0, 50, 100, 200, Infinity],
  default: "other",
  output: { count: { $sum: 1 } }
} }

{ $bucketAuto: { groupBy: "$score", buckets: 5 } }

{ $sortByCount: "$category" }

{ $sort: { createdAt: -1, name: 1 } }

// basic form:
{ $lookup: {
  from: "orders",
  localField: "_id",
  foreignField: "userId",
  as: "userOrders"
} }

// pipeline form (correlated sub-query):
{ $lookup: {
  from: "orders",
  let: { uid: "$_id" },
  pipeline: [
    { $match: {
      $expr: { $eq: ["$userId", "$$uid"] }
    } },
    { $sort: { date: -1 } },
    { $limit: 5 }
  ],
  as: "recentOrders"
} }

{ $graphLookup: {
  from: "employees",
  startWith: "$managerId",
  connectFromField: "managerId",
  connectToField: "_id",
  as: "reportingChain",
  maxDepth: 5,
  depthField: "level"
} }

{ $unionWith: { coll: "archivedOrders" } }
// with a pipeline:
{ $unionWith: {
  coll: "archivedOrders",
  pipeline: [{ $match: { status: "shipped" } }]
} }

{ $setWindowFields: {
  partitionBy: "$department",
  sortBy: { salary: -1 },
  output: {
    rank: { $rank: {} },
    runningTotal: {
      $sum: "$salary",
      window: { documents: ["unbounded", "current"] }
    }
  }
} }

// window forms. Omit window entirely and you get the
// whole partition, which is rarely what you meant.
window: { documents: ["unbounded", "current"] }  // running total
window: { documents: [-3, 0] }                   // this row + 3 before
window: { documents: [0, "unbounded"] }          // this row to the end
window: { range: [-6, "current"], unit: "day" }  // 7 calendar days
window: { range: [-100, 100] }                   // ±100 of the sortBy value

// range counts by the sortBy VALUE, documents counts by
// position. On a series with gaps they disagree, and
// range is almost always the honest one for dates.

{ $facet: {
  priceRanges: [
    { $bucket: { groupBy: "$price", boundaries: [0, 50, 100, 500] } }
  ],
  topRated: [
    { $sort: { rating: -1 } },
    { $limit: 5 }
  ],
  totalCount: [
    { $count: "count" }
  ]
} }

{ $out: "monthlySummary" }
// cross-database (4.4+):
{ $out: { db: "reporting", coll: "monthlySummary" } }

{ $merge: {
  into: "monthlySummary",      // or { db, coll }
  on: "_id",                   // default; needs a unique index
  whenMatched: "merge",        // merge | replace | keepExisting
                               // | fail | [ update pipeline ]
  whenNotMatched: "insert"     // insert | discard | fail
} }

// custom merge logic: only ever grow the stored total
{ $merge: {
  into: "monthlySummary",
  whenMatched: [
    { $set: { revenue: { $max: ["$revenue", "$$new.revenue"] } } }
  ]
} }

{ $search: {
  index: "default",
  text: { query: "coffee", path: "description" }
} }

{ $searchMeta: {
  index: "default",
  facet: {
    operator: { text: { query: "coffee", path: "description" } },
    facets: { categories: { type: "string", path: "category" } }
  }
} }

{ $vectorSearch: {
  index: "vectorIndex",
  path: "embedding",
  queryVector: [0.1, 0.2, 0.3],
  numCandidates: 100,
  limit: 10
} }

{ $listSearchIndexes: {} }
// or one index:
{ $listSearchIndexes: { name: "default" } }

{ $rankFusion: {
  input: {
    pipelines: {
      keyword: [
        { $search: { text: { query: "espresso", path: "desc" } } },
        { $limit: 20 }
      ],
      semantic: [
        { $vectorSearch: {
          index: "vectorIndex", path: "embedding",
          queryVector: [0.1, 0.2, 0.3],
          numCandidates: 200, limit: 20
        } }
      ]
    }
  },
  combination: { weights: { keyword: 1, semantic: 2 } }
} }

{ $scoreFusion: {
  input: {
    pipelines: { keyword: [ ... ], semantic: [ ... ] },
    normalization: "sigmoid"   // none | sigmoid | minMaxScaler
  },
  combination: { method: "avg" }
} }

{ $score: {
  score: { $multiply: [{ $meta: "score" }, "$popularity"] },
  normalization: "minMaxScaler"
} }

{ $redact: {
  $cond: {
    if: { $eq: ["$level", "public"] },
    then: "$$DESCEND",
    else: "$$PRUNE"
  }
} }

{ $densify: {
  field: "date",
  range: { step: 1, unit: "day", bounds: "full" }
} }

{ $fill: {
  sortBy: { date: 1 },
  output: {
    temperature: { method: "linear" },
    status: { value: "unknown" }
  }
} }

// works
db.aggregate([
  { $documents: [
    { name: "Alice", role: "admin" },
    { name: "Bob", role: "user" }
  ] }
])

// fails: $documents is not valid on a collection
db.users.aggregate([{ $documents: [ ... ] }])

{ $changeStream: {
  fullDocument: "updateLookup",
  startAtOperationTime: Timestamp(1690000000, 1)
} }

db.orders.watch(
  [{ $changeStreamSplitLargeEvent: {} }],
  { fullDocument: "required",
    fullDocumentBeforeChange: "required" }
)
// each fragment carries splitEvent: { fragment: 1, of: 3 }

{ $collStats: {
  storageStats: {},
  count: {},
  queryExecStats: {}
} }

{ $indexStats: {} }

{ $planCacheStats: {} }

{ $currentOp: { allUsers: true, idleSessions: false } }

{ $listSessions: { allUsers: true } }

{ $listLocalSessions: { allUsers: true } }

db.getSiblingDB("admin").aggregate([
  { $queryStats: {} }
])

// hash namespaces and field names before exporting
{ $queryStats: {
  transformIdentifiers: {
    algorithm: "hmac-sha-256",
    hmacKey: BinData(8, "...")
  }
} }

db.aggregate([
  { $listSampledQueries: { namespace: "social.post" } }
])

db.getSiblingDB("admin").aggregate([
  { $listClusterCatalog: { shards: true, balancingConfiguration: true } }
])

db.aggregate([
  { $querySettings: { showDebugQueryShape: true } }
])

{ $shardedDataDistribution: {} }