Aggregate pipeline to select all users not found in another documents array-CodePudding

I am wondering if it is possible to use a pipeline to select all users from a user collection that don't exist in an array found in another collection. I have two collections: users and events. I want to get all users that are not already added to an event.

User Schema

{
   _id: ObjectId
   name: string
}

Event Schema

{
   _id: ObjectId
   name: string
   users: ObjectId[]
}

I'm fumbling around with lookups, unwinds, and matches, and know this can be simple without a pipeline, but I want to make sure pagination works and that I don't load 100s of users into RAM (assuming a pipeline is a more efficient way? Maybe I am wrong and the mongodb server would also have to load everything during aggregation).

CodePudding user response：

Here's a solution with lookup.

pipeline = [
  {
    $lookup: {
      from: "users",
      let: { user_ids: "$users" },
      pipeline: [
        { $match: { $expr: { $not: { $in: ["$_id", "$$user_ids"] } } } },
      ],
      as: "AbsentUsers",
    },
  },
];

See it in the Mongodb Playground and kindly refer to the docs yourself.