Fix: MongoDB Aggregation Pipeline Not Working — Wrong Results or Empty Array

The Problem

A MongoDB aggregation pipeline returns an empty array when it shouldn’t:

db.orders.aggregate([
  {
    $lookup: {
      from: 'users',
      localField: 'userId',
      foreignField: '_id',
      as: 'user',
    },
  },
  { $unwind: '$user' },
  { $match: { 'user.role': 'admin' } },
]);
// Returns [] — but matching documents exist

Or $group produces unexpected results:

db.sales.aggregate([
  {
    $group: {
      _id: '$category',
      total: { $sum: 'amount' },  // Returns 0 for all groups
    },
  },
]);

Or $lookup returns an empty array for every document despite matching data existing in the joined collection.

Why This Happens

MongoDB aggregation has several non-obvious behaviors:

• Type mismatch in $lookup — localField is often a string while foreignField (_id) is an ObjectId. These don’t match. The $lookup silently returns an empty array for each document.
• $unwind removes documents with missing or empty arrays — if $lookup returns [] for a document, $unwind drops that document entirely. This silently filters your results.
• $match placement affects performance and results — $match early in the pipeline uses indexes. After $lookup, it operates on the joined documents. Order matters.
• $sum requires a field reference with $ — $sum: 'amount' is a literal string (value 0). $sum: '$amount' is a field reference. The missing $ is a silent bug.
• $group _id must be exact — $group: { _id: '$category' } groups by the exact value. null groups everything together.
• Case sensitivity — field names and string values in $match are case-sensitive. $match: { status: 'Active' } won’t match { status: 'active' }.

Fix 1: Fix $lookup Type Mismatches

The most common $lookup failure is a type mismatch between the joined fields:

// Documents in 'orders' collection:
// { _id: ObjectId("..."), userId: "64a1b2c3d4e5f6789abcdef0" }  ← string

// Documents in 'users' collection:
// { _id: ObjectId("64a1b2c3d4e5f6789abcdef0"), name: "Alice" }  ← ObjectId

// WRONG — string vs ObjectId, $lookup returns [] for every order
db.orders.aggregate([
  {
    $lookup: {
      from: 'users',
      localField: 'userId',    // string: "64a1b2c3d4e5f6789abcdef0"
      foreignField: '_id',     // ObjectId: ObjectId("64a1b2...")
      as: 'user',
    },
  },
]);

Fix option 1: Convert the local field to ObjectId with $addFields:

db.orders.aggregate([
  {
    $addFields: {
      userObjectId: { $toObjectId: '$userId' },  // Convert string → ObjectId
    },
  },
  {
    $lookup: {
      from: 'users',
      localField: 'userObjectId',  // Now matches ObjectId _id
      foreignField: '_id',
      as: 'user',
    },
  },
]);

Fix option 2: Fix the data model — store userId as ObjectId, not string. This is the root cause:

// Fix existing documents in the orders collection
db.orders.find({ userId: { $type: 'string' } }).forEach(doc => {
  db.orders.updateOne(
    { _id: doc._id },
    { $set: { userId: new ObjectId(doc.userId) } }
  );
});

Fix option 3: Use $lookup with a pipeline for more control:

db.orders.aggregate([
  {
    $lookup: {
      from: 'users',
      let: { orderId: { $toObjectId: '$userId' } },
      pipeline: [
        { $match: { $expr: { $eq: ['$_id', '$$orderId'] } } }
      ],
      as: 'user',
    },
  },
]);

Fix 2: Handle $unwind on Missing or Empty Arrays

$unwind drops documents where the field is missing, null, or an empty array by default:

// Orders where $lookup found no matching user → user: []
// $unwind will DROP these documents

// WRONG — silently drops orders with no matching user
db.orders.aggregate([
  { $lookup: { from: 'users', localField: 'userId', foreignField: '_id', as: 'user' } },
  { $unwind: '$user' },  // Drops orders where user: []
]);

// CORRECT — use preserveNullAndEmptyArrays to keep all documents
db.orders.aggregate([
  { $lookup: { from: 'users', localField: 'userId', foreignField: '_id', as: 'user' } },
  {
    $unwind: {
      path: '$user',
      preserveNullAndEmptyArrays: true,  // Keep documents with no match
    },
  },
]);

Debug $unwind results by running the pipeline up to that stage:

// Run only the $lookup stage to check what 'user' contains
db.orders.aggregate([
  { $lookup: { from: 'users', localField: 'userId', foreignField: '_id', as: 'user' } },
  { $limit: 5 },  // Check a sample
]);
// If user: [] for all documents → type mismatch in $lookup
// If user: [{ ... }] → $lookup works, check $unwind

Fix 3: Fix $group and $sum

Missing $ prefix on field references is a silent bug — $sum treats a bare string as a literal value:

// WRONG — 'amount' is a string literal (not a field reference)
// $sum of string literal = 0
db.sales.aggregate([
  {
    $group: {
      _id: '$category',
      total: { $sum: 'amount' },    // ← Missing $, sums the string 'amount' = 0
      count: { $sum: 1 },           // ← Literal 1, this is correct for counting
    },
  },
]);

// CORRECT — use $ prefix for field references
db.sales.aggregate([
  {
    $group: {
      _id: '$category',
      total: { $sum: '$amount' },   // ← $amount references the 'amount' field
      avgPrice: { $avg: '$price' },
      maxQty: { $max: '$quantity' },
      items: { $push: '$name' },    // Collect all names into an array
    },
  },
]);

Group by multiple fields:

db.sales.aggregate([
  {
    $group: {
      _id: {
        category: '$category',
        year: { $year: '$date' },
      },
      total: { $sum: '$amount' },
    },
  },
  { $sort: { '_id.year': -1, total: -1 } },
]);

Group all documents (no grouping key):

db.sales.aggregate([
  {
    $group: {
      _id: null,           // null groups everything into one document
      grandTotal: { $sum: '$amount' },
      docCount: { $sum: 1 },
    },
  },
]);

Fix 4: Optimize $match Placement

$match early in the pipeline uses indexes and filters documents before expensive operations like $lookup:

// WRONG — $match after $lookup processes all documents first
db.orders.aggregate([
  {
    $lookup: {
      from: 'users',
      localField: 'userId',
      foreignField: '_id',
      as: 'user',
    },
  },
  { $match: { status: 'completed' } },  // Filters AFTER joining all users
]);

// CORRECT — filter early, join fewer documents
db.orders.aggregate([
  { $match: { status: 'completed' } },  // Uses index, reduces documents
  {
    $lookup: {
      from: 'users',
      localField: 'userId',
      foreignField: '_id',
      as: 'user',
    },
  },
]);

$match with $expr for comparing fields:

// Match documents where endDate > startDate (compare two fields in same doc)
db.events.aggregate([
  {
    $match: {
      $expr: { $gt: ['$endDate', '$startDate'] },
    },
  },
]);

// After $lookup, match on joined field
db.orders.aggregate([
  { $lookup: { from: 'users', localField: 'userId', foreignField: '_id', as: 'user' } },
  { $unwind: { path: '$user', preserveNullAndEmptyArrays: false } },
  { $match: { 'user.active': true } },  // Filter on joined field
]);

Fix 5: Use $project and $addFields Correctly

Control which fields appear in the output:

db.orders.aggregate([
  { $match: { status: 'completed' } },
  {
    $lookup: {
      from: 'users',
      localField: 'userId',
      foreignField: '_id',
      as: 'user',
    },
  },
  { $unwind: '$user' },
  {
    $project: {
      // 1 = include, 0 = exclude
      orderId: '$_id',        // Rename _id to orderId
      amount: 1,              // Include amount
      status: 1,              // Include status
      customerName: '$user.name',   // Promote nested field
      customerEmail: '$user.email',
      _id: 0,                 // Exclude _id from output
      user: 0,                 // Exclude the full user object
    },
  },
]);

// $addFields — add computed fields without removing others
db.orders.aggregate([
  {
    $addFields: {
      totalWithTax: { $multiply: ['$amount', 1.1] },
      isLargeOrder: { $gte: ['$amount', 1000] },
      year: { $year: '$createdAt' },
    },
  },
]);

Fix 6: Debug with $explain and Stage-by-Stage

Break a complex pipeline into stages to find where it goes wrong:

// Step 1: Run only the first stage
db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $limit: 5 },
]).toArray();

// Step 2: Add the next stage
db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $lookup: { from: 'users', localField: 'userId', foreignField: '_id', as: 'user' } },
  { $limit: 5 },
]).toArray();

// Step 3: Check what 'user' looks like — empty array or populated?
// Continue adding stages until you find the stage that breaks results

// Check if the pipeline uses indexes
db.orders.explain('executionStats').aggregate([
  { $match: { status: 'completed' } },
  { $lookup: { from: 'users', localField: 'userId', foreignField: '_id', as: 'user' } },
]);
// Look for 'IXSCAN' (index scan) vs 'COLLSCAN' (full scan)

Count documents at each stage:

// Add a $count stage to verify documents aren't being filtered out
db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $count: 'afterMatch' },
]);

db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $lookup: { from: 'users', localField: 'userId', foreignField: '_id', as: 'user' } },
  { $count: 'afterLookup' },
]);

// If afterMatch > 0 but afterLookup = 0 → problem is in the $lookup

Fix 7: Common Aggregation Patterns

Frequently used patterns that are easy to get wrong:

// Pagination with $skip and $limit
db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $sort: { createdAt: -1 } },
  { $skip: 20 },   // Skip first 20
  { $limit: 10 },  // Take next 10
]);

// Faceted search — multiple aggregations in parallel
db.products.aggregate([
  { $match: { inStock: true } },
  {
    $facet: {
      byCategory: [
        { $group: { _id: '$category', count: { $sum: 1 } } },
        { $sort: { count: -1 } },
      ],
      priceStats: [
        { $group: { _id: null, avg: { $avg: '$price' }, max: { $max: '$price' } } },
      ],
      total: [{ $count: 'count' }],
    },
  },
]);

// Date-based grouping
db.sales.aggregate([
  {
    $group: {
      _id: {
        year: { $year: '$date' },
        month: { $month: '$date' },
      },
      monthlyRevenue: { $sum: '$amount' },
    },
  },
  { $sort: { '_id.year': 1, '_id.month': 1 } },
]);

// Array operations — filter and transform array fields
db.posts.aggregate([
  {
    $project: {
      title: 1,
      // Filter tags array to only include specific values
      techTags: {
        $filter: {
          input: '$tags',
          as: 'tag',
          cond: { $in: ['$$tag', ['javascript', 'typescript', 'python']] },
        },
      },
      tagCount: { $size: '$tags' },
    },
  },
]);

Still Not Working?

$lookup returns multiple documents when you expect one — if the foreign collection has duplicate values in foreignField, $lookup returns all matches as an array. Use $unwind to flatten, or $arrayElemAt: ['$user', 0] to take the first element.

Aggregation is slow — use $match and $sort as early as possible. Ensure indexes exist on fields used in $match, $lookup localField/foreignField, and $sort. Run .explain('executionStats') to verify index usage.

$lookup across databases — $lookup can only join collections within the same database. To join across databases, use $merge to copy data to the same database first, or perform the join in application code.

Numbers stored as strings — if numeric fields were imported as strings, $sum and $avg return 0. Convert with $toDouble or $toInt in a $addFields stage before grouping.

For related MongoDB issues, see Fix: MongoDB Duplicate Key Error and Fix: MongoDB Schema Validation Error.