Mongoose聚合$lookup无法填充嵌套数组的notificationTemplate字段

我有如下的Notifications Schema：

const mongoose = require("mongoose");

var NotificationsSchema = mongoose.Schema({
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "User", //this may be null if user is not in system
  },
  userEmail: {
    type: String,
    //required: [true, "User Email cannot be empty"],
  },
  notifications: [
    {
      _id: mongoose.Schema.Types.ObjectId,
      notificationTemplate: {
        type: mongoose.Schema.Types.ObjectId,
        ref: "NotificationTemplate",
        required: [true, "Notification Template cannot be empty"],
      },
      url: {
        type: String,
        //required: [true, "Message cannot be empty"],
      },
      attachment: {
        type: [String],
      },
      by: {
        type: mongoose.Schema.Types.ObjectId,
        ref: "User", //if some other user send me notification
      },
      protocols: {
        type: [
          {
            type: String,
            enum: ["email", "webapp", "smstext"],
          },
        ],
        validate: {
          validator: function (protocols) {
            return protocols.length > 0;
          },
          message: "At least one protocol is required",
        },
        required: [true, "Protocols for Notification cannot be empty"],
      },
      isRead: {
        type: Boolean,
        default: false,
      },
      readOn: {
        type: Date,
      },
      sentWeb: {
        type: Date,
      },
      sentEmail: {
        type: Date,
      },
      sentSmsText: {
        type: Date,
      },
      isDeleted: {
        type: Boolean,
        default: false,
      },
      //Make sure to save all subject and message variable in notificationParameters
      notificationParameters: Object, //here saved any other info
      createdOn: {
        type: Date,
        default: Date.now(),
      },
    },
  ],
  createdDate: {
    type: Date,
    default: Date.now(),
  },
});

let Notifications;

if (!mongoose.models["Notifications"]) {
  Notifications = mongoose.model("Notifications", NotificationsSchema);
} else {
  Notifications = mongoose.models["Notifications"];
}
module.exports = Notifications;

尝试用聚合查询实现用户通知分页，同时填充notificationTemplate字段，但该字段始终是ObjectId，没有被填充。每个用户在Notifications集合中可能有多条文档。

我的聚合查询代码：

const doc = await Notifications.aggregate([
    { $match: { user: mongoose.Types.ObjectId(req.params.userId) } },
    //{ $unwind: "$notifications" },
    { $match: { "notifications.protocols": { $in: ["webapp"] } } },
    {
      $lookup: {
        localField: "notificationTemplate",
        //localField: "notifications.notificationTemplate", //not working
        foreignField: "_id",
        from: "NotificationTemplate", // Replace with the actual collection name
        as: "notificationTemplate",
        // as: "notificationTemplateInfo",//not working
      },
    },
    { $sort: { "notifications.createdOn": -1 } }, // Sort by createdOn field in descending order
    { $skip: (page - 1) * parseInt(itemsPerPage) },
    { $limit: parseInt(itemsPerPage) },
    { $project: { notifications: 1 } },
    //{ $unwind: "$notifications" },
 ]);

返回的数据示例：

[
   {
    attachment: [],
    protocols: [ 'email', 'webapp' ],
    isRead: false,
    isDeleted: false,
    createdOn: 2023-11-03T16:46:59.372Z,
    notificationTemplate: 65451f5277306d1d2c418cd5,
    url: '/session/654524e33e894e35ac4c00b9',
    by: 63a66a814e32a254204776e8,
    sentWeb: 2023-11-03T16:50:43.769Z,
    notificationParameters: { subject: 'Networking iDea' },
    sentEmail: 2023-11-03T16:50:45.766Z,
    _id: 654524e59c36fe07f08542c5
  }
]

MongoDB中的样本数据：

{
    "_id":{"$oid":"65461f21db7f106a02931cc2"},
    "createdDate":{"$date":{"$numberLong":"1699030019372"}},
    "user":{"$oid":"63a24f8a9d7babc0c0e51743"},
    "userEmail":"s.m.sami125@gmail.com",
    "notifications":[{
      "attachment":[],
      "protocols":["email","webapp"],
      "isRead":false,
      "isDeleted":false,
      "createdOn":{"$date":{"$numberLong":"1699030019372"}},
      "notificationTemplate":{"$oid":"65451f5277306d1d2c418cd5"},
      "url":"/session/654524e33e894e35ac4c00b9",
      "by":{"$oid":"63a66a814e32a254204776e8"},
      "sentWeb":{"$date":{"$numberLong":"1699030243769"}},
      "notificationParameters":{"subject":"Networking iDea"},
      "sentEmail":{"$date":{"$numberLong":"1699030245766"}},
      "_id":{"$oid":"654524e59c36fe07f08542c5"}
    }]
}

但使用find结合populate可以正常填充：

const c = await Notifications.find({ user: req.params.userId }).populate(
    "notifications.notificationTemplate"
  );

问题原因 #

聚合查询的$lookup配置错误：notificationTemplate是嵌套在notifications数组的子文档内的字段，直接使用notificationTemplate作为localField无法匹配到目标值，且未先展开数组，导致关联查询无法生效。

修正后的聚合查询 #

const doc = await Notifications.aggregate([
  // 匹配目标用户的通知文档
  { $match: { user: mongoose.Types.ObjectId(req.params.userId) } },
  // 展开notifications数组，将每个子文档转为独立文档
  { $unwind: "$notifications" },
  // 筛选出包含webapp协议的通知
  { $match: { "notifications.protocols": { $in: ["webapp"] } } },
  // 关联查询NotificationTemplate集合，填充嵌套的notificationTemplate
  {
    $lookup: {
      localField: "notifications.notificationTemplate",
      foreignField: "_id",
      from: "NotificationTemplate",
      as: "notifications.notificationTemplate"
    }
  },
  // 将lookup返回的数组转为单个文档（和populate结构一致）
  {
    $set: {
      "notifications.notificationTemplate": { $first: "$notifications.notificationTemplate" }
    }
  },
  // 按通知创建时间倒序排序
  { $sort: { "notifications.createdOn": -1 } },
  // 分页：跳过指定数量的文档
  { $skip: (page - 1) * parseInt(itemsPerPage) },
  // 限制返回的文档数量
  { $limit: parseInt(itemsPerPage) },
  // 重新将通知组合为数组（保持原文档结构）
  {
    $group: {
      _id: "$_id",
      user: { $first: "$user" },
      userEmail: { $first: "$userEmail" },
      notifications: { $push: "$notifications" },
      createdDate: { $first: "$createdDate" }
    }
  },
  // 只返回需要的字段
  { $project: { notifications: 1 } }
]);

关键步骤说明 #

1. $unwind展开数组：必须先将notifications数组展开，才能对其中的子文档字段执行关联查询，因为$lookup默认只识别顶层文档的字段。
2. 正确设置localField：使用notifications.notificationTemplate作为关联的本地字段，精准指向数组子文档中的ObjectId。
3. 处理$lookup返回结果：$lookup会返回一个数组，通过$set结合$first将其转为单个文档，和populate的返回结构保持一致。
4. $group重组数组：如果需要保持原文档的数组结构，用$group将展开后的单个通知文档重新组合为数组；若不需要数组结构，可省略此步骤。

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