MongoDB聚合$lookup用pipeline时，如何正确匹配本地与外部字段？

问题背景 #

我有两个MongoDB集合：emailtemplates和emails，需要统计每个邮件模板的发送次数，必须包含从未被使用过的模板。直接统计emails集合会遗漏未发送的模板，因此需从emailtemplates关联emails。

原本使用$lookup的localField和foreignField参数可实现需求，但因需添加额外逻辑，必须改用pipeline选项——此时不能再指定localField或foreignField，否则会触发错误：MongoServerError: $lookup with 'pipeline' may not specify 'localField' or 'foreignField'。

我编写了包含pipeline的$lookup查询，但结果不符合预期：所有emailtemplates文档都关联了全部emails记录，而非仅匹配templateName与name字段的邮件。

示例文档 #

// emailtemplates集合示例文档
[
  {
    "name": "WELCOME-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{"name": "firstname","type": "string","required": true},{"name": "lastname","type": "string","required": true}],
    "body":"Dear {firstname}, Welcome to the club"
  },
  {
    "name": "GOODBYE-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{"name": "firstname","type": "string","required": true},{"name": "lastname","type": "string","required": true}],
    "body":"Dear {firstname}, were sorry to see you go"
  },
  {
    "name": "YOURE-FIRED-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{"name": "firstname","type": "string","required": true},{"name": "lastname","type": "string","required": true}],
    "body":"Dear {firstname}, Pack your stuff and go"
  }
]

// emails集合示例文档
[
  {
    "templateName": "WELCOME-01",
    "recipient": "john.doe@gmail.com",
    "parameters": {"firstName": "John","lastName": "Doe"}
  },
  {
    "templateName": "WELCOME-01",
    "recipient": "david.chappelle@gmail.com",
    "parameters": {"firstName": "David","lastName": "Chappelle"}
  },
  {
    "templateName": "GOODBYE-01",
    "recipient": "the.joker@gmail.com",
    "parameters": {"firstName": "The","lastName": "Joker"}
  }
]

错误查询代码 #

db.emailtemplates.aggregate([
  { $match:{channel: 'email'} },
  {
    $lookup: {
      from: "emails",
      let: {
        templateName: "$templateName",
        name: "$name"
      },
      pipeline: [
        { $match: { $expr: {$eq: [ "$$templateName","$name"] } } },
        { $project:{"templateName":"$templateName","name":"$name"} }
      ],
      as: "emails"
    }
  }
])

错误结果 #

所有emailtemplates文档的emails字段都包含全部3条邮件记录，而非仅匹配的邮件。

期望输出 #

每个emailtemplates文档的emails字段仅包含templateName与自身name匹配的邮件，未匹配的则为空数组；同时可直接得到每个模板的发送次数。

解决方案 #

问题出在$lookup的变量定义和匹配逻辑错误：

1. let中错误定义了不存在的templateName字段，需改为传递当前模板的name
2. $match中的字段匹配方向搞反，需匹配emails的templateName等于emailtemplates的name

正确聚合管道代码 #

db.emailtemplates.aggregate([
  { $match: { channel: 'email' } },
  {
    $lookup: {
      from: "emails",
      let: { template_name: "$name" }, // 将当前模板的name作为变量传入子管道
      pipeline: [
        {
          $match: {
            $expr: { $eq: [ "$templateName", "$$template_name" ] } // 匹配emails的templateName与当前模板name一致的记录
          }
        },
        // 此处可添加你需要的额外逻辑，比如字段投影、过滤条件等
        { $project: { templateName: 1, recipient: 1, _id: 0 } }
      ],
      as: "emails"
    }
  },
  // 可选：添加发送次数统计阶段
  {
    $addFields: {
      send_count: { $size: "$emails" } // 计算关联邮件的数量，即发送次数
    }
  }
])

代码说明 #

• let关键字定义变量template_name，值为当前emailtemplates文档的name字段，供子管道使用
• 子管道的$match通过$expr实现跨集合字段匹配，确保仅关联对应模板的邮件记录
• 最后通过$addFields和$size直接统计每个模板的发送次数，同时保留关联的邮件明细

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