如何在MongoDB聚合中仅保留关联users数组的指定字段？

Got it, let's tackle that redundant users array problem in your MongoDB aggregation query! The issue here is that your current lookup pulls in full user documents, but you only want specific fields. Here are two clean, efficient ways to fix this:

This approach handles both filtering type: 'agent' users and trimming down to your desired fields before pulling the data into your agency results. It’s more efficient because it reduces the amount of data transferred and processed early on.

Let’s say you want to keep only the user's _id, name, and email fields. Here’s the modified query:

agencyTable.aggregate([
  { $match: {} }, // Keep your existing match logic here
  { $sort: { activityDate: 1 } },
  {
    $lookup: {
      from: "users",
      let: { agencyId: "$_id" },
      pipeline: [
        // Match users linked to the agency AND of type 'agent'
        { $match: { 
          $expr: { $eq: ["$agency", "$$agencyId"] },
          type: "agent"
        }},
        // Project only the fields you need (1 = keep, 0 = exclude)
        { $project: {
          _id: 1,
          name: 1,
          email: 1,
          agency: 0 // Optional: exclude the agency foreign key to reduce redundancy
        }}
      ],
      as: "users"
    }
  },
  {
    $group: {
      "_id": "$_id",
      "phone": { "$first": "$phone" },
      "activityDate": { "$first": "$activityDate" }, // Fill in your original logic here (e.g., $first/$last)
      "users": { $push: "$users" } // Aggregate the filtered, trimmed user array
    }
  }
])

If you prefer not to modify the lookup’s internal logic, you can clean up the users array after the lookup using $project. This combines filtering for agent types and mapping to your desired fields:

agencyTable.aggregate([
  { $match: {} },
  { $sort: { activityDate: 1 } },
  {
    $lookup: {
      from: "users",
      localField: "_id",
      foreignField: "agency",
      as: "users"
    }
  },
  {
    $project: {
      phone: 1,
      activityDate: 1,
      // First filter users to only agents, then map to keep desired fields
      users: {
        $map: {
          input: {
            $filter: {
              input: "$users",
              cond: { $eq: ["$$this.type", "agent"] }
            }
          },
          as: "user",
          in: {
            _id: "$$user._id",
            name: "$$user.name",
            email: "$$user.email"
          }
        }
      }
    }
  },
  {
    $group: {
      "_id": "$_id",
      "phone": { "$first": "$phone" },
      "activityDate": { "$first": "$activityDate" },
      "users": { $first: "$users" } // Pull in the cleaned-up users array
    }
  }
])

为什么原查询会有冗余？ #

Your initial lookup pulls in full user documents, then unwinds and matches—but never trims down the user fields. By adding field filtering either in the lookup pipeline or post-lookup project, you eliminate all that extra, unnecessary data from the final users array.

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