Remove object of array schema if the ID does not exist from another array schema

Question

I do have 2 different schemas, one for the user and one for the jobs. Job has something like manage which is an array of objects and there goes the ID of the user that applied for that job and the ID of the job into user which has an array messages. If I delete that Object of manage from database then when I get the messages of the user I want to check if that my ID it is into manage, if not then $pull/delete the object with the remaining ID.

Below is what I tried

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 },
 },
],
}

This is the function of the controller which is getting called from the API.

    async userJobMessages(req, res , next) {
                //req.params.id it is always the authenticated 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,
        });
    },

I tried somehow to find if my ID exists into manage if not then delete that jobID from my messages.

Based on what @nimrodserok said this is the following which still not working. I am getting an error message like this. (node:47922) UnhandledPromiseRejectionWarning: MongoError: let not supported

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,
        });
    },

Answer 1

One option is to do it on a single query with $lookup:

1. Fins the relevant user
2. Using $lookup, find all jobs listed on this user, but keep only the _ids of jobs that the user is listed on their manage_. Assign them to jobs key.
3. Filter the user messages to keep only jobs with _ids from our jobs list.
4. $unset the jobs key
5. $merge back into the users collection to update the user

const user = await 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"}}
])

See how it works on the playground example

EDIT:

For mongoDB version 4.2 or older you can use playground example