如何在Mongoose中编写分组查询，获取组织各机会状态对应机会列表

Alright, let's work through this to get you the exact output you need. The goal is to take your organisation collection's opportunitystatus list, and match each status to the corresponding opportunities from the leads collection—while preserving statuses that don't have any matching opportunities.

Key Note: Status Name Discrepancy #

First, I noticed your data has a small mismatch: the organisation uses "Review Proposal" but the leads have opportunities with status "Reviewing Proposal". I've included handling for this in the queries below; you can adjust this if it's just a typo.

Option 1: Two-Step Query (Simpler to Read) #

This approach first fetches the organisation's status list, then aggregates leads to group opportunities by status, and finally merges the results to include all statuses.

const mongoose = require('mongoose');
const ObjectId = mongoose.Types.ObjectId;

// Replace with your target organization ID
const targetOrgId = ObjectId("5c756b4ea5a8cd1b0d3fbfe7");

// Step 1: Get all opportunity statuses from the target organization
const targetOrg = await Organisation.findById(targetOrgId);
const orgStatuses = targetOrg.opportunitystatus.map(status => status.name);

// Step 2: Aggregate leads to group opportunities by matching status
const opportunitiesGrouped = await Lead.aggregate([
  // Filter leads for the target organization
  { $match: { organization_id: targetOrgId } },
  // Unwind the opportunities array to work with individual opportunities
  { $unwind: "$opportunities" },
  // Group opportunities by their mapped status (handles the "Review Proposal" / "Reviewing Proposal" mismatch)
  {
    $group: {
      _id: {
        $switch: {
          branches: [
            { case: { $eq: ["$opportunities.status", "Call Scheduled"] }, then: "Call Scheduled" },
            { case: { $eq: ["$opportunities.status", "Reviewing Proposal"] }, then: "Review Proposal" }
          ],
          default: null // Ignore any statuses not defined in the organization
        }
      },
      opportunities: { $push: "$opportunities" }
    }
  },
  // Only keep groups that match the organization's status list
  { $match: { _id: { $in: orgStatuses } } },
  // Reshape the output to match your desired format
  { $project: { _id: 0, name: "$_id", opportunities: 1 } }
]);

// Step 3: Merge the organization's status list with grouped opportunities
const finalResult = {
  opportunitystatus: orgStatuses.map(statusName => {
    const matchedGroup = opportunitiesGrouped.find(group => group.name === statusName);
    // Return the group if it exists, otherwise just the status name
    return matchedGroup || { name: statusName };
  })
};

console.log(finalResult);

Option 2: Single Aggregation Pipeline (All-in-One) #

If you prefer a single pipeline that joins the collections directly, this uses $lookup to associate the organisation's statuses with leads opportunities:

const mongoose = require('mongoose');
const ObjectId = mongoose.Types.ObjectId;

const targetOrgId = ObjectId("5c756b4ea5a8cd1b0d3fbfe7");

const finalResult = await Organisation.aggregate([
  // Match the target organization
  { $match: { _id: targetOrgId } },
  // Unwind the opportunitystatus array to process each status individually
  { $unwind: "$opportunitystatus" },
  // Lookup and filter opportunities from leads that match the current status
  {
    $lookup: {
      from: "leads",
      let: { currentStatus: "$opportunitystatus.name" },
      pipeline: [
        { $match: { organization_id: targetOrgId } },
        { $unwind: "$opportunities" },
        // Match opportunities to the current status (handles the name mismatch)
        {
          $match: {
            $expr: {
              $or: [
                { $eq: ["$opportunities.status", "$$currentStatus"] },
                { $and: [{ $eq: ["$$currentStatus", "Review Proposal"] }, { $eq: ["$opportunities.status", "Reviewing Proposal"] }] }
              ]
            }
          }
        },
        // Extract just the opportunity data
        { $replaceRoot: { newRoot: "$opportunities" } }
      ],
      as: "opportunities"
    }
  },
  // Group back into the original opportunitystatus array format
  {
    $group: {
      _id: "$_id",
      opportunitystatus: {
        $push: {
          name: "$opportunitystatus.name",
          // Only include the opportunities field if there are matching opportunities
          opportunities: { $cond: [{ $eq: ["$opportunities", []] }, "$$REMOVE", "$opportunities"] }
        }
      }
    }
  },
  // Remove the organization ID from the final output
  { $project: { _id: 0 } }
]);

console.log(finalResult[0]);

How It Works #

• Both approaches ensure that all statuses from the organisation are included, even if there are no matching opportunities.
• The status name mapping handles the discrepancy between "Review Proposal" (organisation) and "Reviewing Proposal" (leads). If this is a typo, you can remove that conditional and just use a direct $eq match.
• The output will exactly match the format you specified, with each status either having an opportunities array or just the name field if no opportunities exist.

内容的提问来源于stack exchange，提问作者Ranjan Adhikari