求助：移除用户消息中无对应Job manage_记录的条目

我有User和Job两个Schema：

• Job的manage_字段是存储申请用户ID的对象数组
• User的messages字段是包含jobId的消息数组

需求：当删除Job的manage_中某用户记录后，获取该用户消息时，需检查用户ID是否仍在对应Job的manage_中，若不存在则从messages中删除对应jobId的消息对象。

现有Schema定义 #

Job Schema #

manage_: [{
   _id: false,
   createdAt: Date,
   userId: String,
   contactedID: String,
   listPosition: Number,
   note: String,
}]

User Schema #

{
 firstName: String,
 lastName: String,
 messages: [{
   _id: mongoose.Schema.Types.ObjectId,
   userId: String,
   messageID: String,
   jobId: String,
   timestamp: { type: Date, default: Date.now },
 },
],
}

尝试过的方案及问题 #

初始控制器函数 #

async userJobMessages(req, res , next) {
    // req.params.id 为已认证用户ID
    let user = await User.findById(req.params.id);
    let fromDate = new Date(Date.now() - 60 * 60 * 24 * 30 * 1000);
    user.messages.map(async (t) => {
        let job = await Job.find({ _id: t.jobId });

        console.log(job, "T")
        if (job.length < 1) {
            let test2 = await User.findByIdAndUpdate(req.params.id, {
                $pull: {
                    messages: {
                        jobId: t.jobId,
                    }
                }
            }, { new: true });

        }});

    res.status(200).json({
        messages: user.messages,
    });
},

该方案未实现预期效果：异步循环未完成就返回响应，且仅检查Job是否存在，未验证用户是否在Job的manage_数组中。

修改后的聚合查询代码 #

async userJobMessages(req, res , next) {
    let user = await User.findById(req.params.id);
    let fromDate = new Date(Date.now() - 60 * 60 * 24 * 30 * 1000);

    User.aggregate([
        {$match: {_id: req.params.id}},
        {$lookup: {
            from: "jobs",
            let: {jobIds: "$messages.jobId", userId: {$first: "$messages.userId"}},
            pipeline: [
              {$match: {$expr: {$in: [{$toString: "$_id"}, "$$jobIds"]}}},
              {$project: {manage_: {
                    $filter: {
                      input: "$manage_",
                      cond: {$eq: ["$$this.userId", "$$userId"]}
                    }
              }}},
              {$match: {"manage_.0": {$exists: true}}},
              {$project: {_id: {$toString: "$_id"}}}
            ],
            as: "jobs"
        }},
        {$set: {
            messages: {$filter: {
                input: "$messages",
                cond: {$in: ["$$this.jobId", "$jobs._id"]}
            }}
        }},
        {$unset: "jobs"},
        {$merge: {into: "users"}}
      ])

    res.status(200).json({
        messages: user.messages,
    });
},

执行报错：(node:47922) UnhandledPromiseRejectionWarning: MongoError: let not supported，且逻辑存在错误（取第一条消息的userId而非当前请求用户ID）。

报错原因 #

let关键字仅在MongoDB 3.6及以上版本支持，若版本低于3.6则会触发该错误；同时原聚合逻辑中获取userId的方式不符合需求。

修正方案 #

方案一：异步循环处理（适合小数据量） #

async userJobMessages(req, res, next) {
    const userId = req.params.id;
    let user = await User.findById(userId);
    if (!user) return res.status(404).json({ message: "用户不存在" });

    // 收集用户仍在manage_中的jobId
    const validJobIds = [];
    for (const msg of user.messages) {
        const job = await Job.findById(msg.jobId);
        if (job) {
            const isUserInManage = job.manage_.some(item => item.userId === userId);
            if (isUserInManage) {
                validJobIds.push(msg.jobId);
            }
        }
    }

    // 过滤并保存有效消息
    user.messages = user.messages.filter(msg => validJobIds.includes(msg.jobId));
    await user.save();

    res.status(200).json({ messages: user.messages });
}

方案二：兼容低版本MongoDB的聚合查询 #

async userJobMessages(req, res, next) {
    const userId = req.params.id;
    const [updatedUser] = await User.aggregate([
        { $match: { _id: mongoose.Types.ObjectId(userId) } },
        // 关联所有相关Job
        {
            $lookup: {
                from: "jobs",
                localField: "messages.jobId",
                foreignField: "_id",
                as: "relatedJobs"
            }
        },
        // 筛选用户仍在manage_中的Job ID
        {
            $addFields: {
                validJobIds: {
                    $map: {
                        input: {
                            $filter: {
                                input: "$relatedJobs",
                                cond: {
                                    $in: [userId, { $map: { input: "$$this.manage_", as: "item", in: "$$item.userId" } }]
                                }
                            }
                        },
                        as: "job",
                        in: { $toString: "$$job._id" }
                    }
                }
            }
        },
        // 过滤保留有效消息
        {
            $addFields: {
                messages: {
                    $filter: {
                        input: "$messages",
                        cond: { $in: ["$$this.jobId", "$validJobIds"] }
                    }
                }
            }
        },
        // 移除临时字段并更新数据库
        { $unset: ["relatedJobs", "validJobIds"] },
        { $merge: { into: "users", on: "_id" } }
    ]);

    res.status(200).json({ messages: updatedUser?.messages || [] });
}

方案三：MongoDB 3.6+ 修正后的聚合查询 #

async userJobMessages(req, res, next) {
    const userId = req.params.id;
    const [updatedUser] = await User.aggregate([
        { $match: { _id: mongoose.Types.ObjectId(userId) } },
        {
            $lookup: {
                from: "jobs",
                let: { jobIds: "$messages.jobId", currentUserId: userId },
                pipeline: [
                    { $match: { $expr: { $in: [{ $toString: "$_id" }, "$$jobIds"] } } },
                    // 筛选用户仍在manage_中的Job
                    {
                        $match: {
                            $expr: {
                                $in: ["$$currentUserId", { $map: { input: "$manage_", as: "item", in: "$$item.userId" } }]
                            }
                        }
                    },
                    { $project: { _id: { $toString: "$_id" } } }
                ],
                as: "validJobs"
            }
        },
        // 过滤保留有效消息
        {
            $addFields: {
                messages: {
                    $filter: {
                        input: "$messages",
                        cond: { $in: ["$$this.jobId", "$validJobs._id"] }
                    }
                }
            }
        },
        // 移除临时字段并更新数据库
        { $unset: "validJobs" },
        { $merge: { into: "users", on: "_id" } }
    ]);

    res.status(200).json({ messages: updatedUser?.messages || [] });
}

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