MongoDB Join

MongoDB Join in Node.js: Aggregation Pipelines with $lookup

Although MongoDB encourages embedding tightly coupled data inside one document, normalized references (like orders.userId pointing to users._id) still appear often. MongoDB joins documents across two collections on the database server using the $lookup stage inside collection.aggregate().

Joining Collections with $match, $lookup, and $unwind

Place $match at the very start of your pipeline so MongoDB filters records using indexes before joining. After $lookup attaches an array of matching child documents, $unwind (with preserveNullAndEmptyArrays: true for left-join behavior) flattens single matches into an object, and $project keeps only safe fields.

flowchart LR
    A["orders ($match status: paid)"] --> B["$lookup from: users"]
    B --> C["$unwind customer (preserveNull)"]
    C --> D["$project safe response fields"]

async function listOrdersWithCustomers(ordersCollection) {
  const pipeline = [
    { $match: { status: 'paid' } },
    { $sort: { createdAt: -1 } },
    { $limit: 20 },
    {
      $lookup: {
        from: 'users',
        localField: 'userId',
        foreignField: '_id',
        as: 'customer',
      },
    },
    {
      $unwind: {
        path: '$customer',
        preserveNullAndEmptyArrays: true,
      },
    },
    {
      $project: {
        _id: 1,
        totalAmount: 1,
        createdAt: 1,
        customerName: '$customer.fullName',
        customerEmail: '$customer.email',
      },
    },
  ];

  return ordersCollection.aggregate(pipeline).toArray();
}

Keeping $lookup Fast in Production

Always create an index on foreignField in the target collection (users._id is already indexed, while comments.postId needs an explicit index) so $lookup does not scan the entire foreign collection for every input document.

Common Mistakes to Avoid

• Omitting preserveNullAndEmptyArrays: true on $unwind: By default, $unwind: '$customer' silently drops any order whose customer array is empty (acting like an INNER JOIN instead of a LEFT JOIN).
• Putting $match and $limit after $lookup: Joining 100,000 orders before filtering down to 20 paid rows wastes enormous CPU and memory. Filter and limit as early as possible.
• Exposing passwordHash from the joined users document: Always add a $project stage (or pipeline inside $lookup) to strip sensitive fields.

Frequently Asked Questions

Can $lookup filter or sort the joined collection before returning it?

Yes. Using the pipeline syntax inside $lookup with let variables lets you run $match, $sort, $limit, and $project directly on the joined collection.

When should I embed sub-documents vs using $lookup?

Embed data that is bounded and always read together (like shipping address or invoice line items). Reference by ID and join with $lookup when the child collection grows unboundedly or is shared across many records (like users or products).

Does $lookup work with sharded collections?

Yes, modern MongoDB versions support $lookup on sharded collections, though co-locating or embedding hot read paths remains faster at very high scale.