How to get one random row at a time from one collection after looking up entries in another collection?

Users collection

1) _id: abc, name: abc, group: 1
2) _id: xyz, name: xyyy, group: 3
3) _id: 123, name: yyy, group: 1
4) _id: rrr, name: tttt, group: 1
5) _id: eee, name: uuu, group: 1

Partnership Collection

1) _id: abc_123, fromUser: abc, toUser: 123

Mongo query to find random user from users collection where

• 1. _id not the req.query.Id (for example - not abc) AND
• 2. group matches the group of user (req.query.Id) AND
• 3. an entry with both users does not already exist in Partnership collection - In above example, the user 123 will be ignored because its already in partnership collection either as fromUser or toUser

I started a query but need some help to proceed:

  const users = await req.db
    .collection('users')
    .aggregate( [{
      $match:{
        group: group //group of req.user._id      
        },
    },
    {
      $lookup: {
        from: "partnership",
        let: {
          userId: "$fromUser"
        },
        as: "userDetails",
        pipeline: [
          {
            $match: {
              $expr: {
                $eq: [
                  "$$userId",
                  "$_id"
                ],
                
              }
            }
          },....

CodePudding user response：

One option to continue is:

1. From the $lookup get only documents which have a forbidden connection
2. $match only users with no forbidden connection. Now we have only valid documents
3. Use $rand to get a random document (You may consider to use $sample, but only if you expect more than 100 documents as a response)

db.users.aggregate([
  {$match: {
      group: group ,
      _id: {$ne: user_id}
    }
  },
  {$lookup: {
      from: "partnership",
      let: {userId: "$_id"},
      as: "prob",
      pipeline: [
        {$set: {users: ["$fromUser", "$toUser"]}},
        {$match: {
            $expr: {
              $and: [{$in: ["$$userId", "$users"]}, {$in: [user_id, "$users"]}]
            }
          }
        }
      ]
    }
  },
  {$match: {"prob.0": {$exists: false}}},
  {$set: {prob: {$rand: {}}}},
  {$sort: {prob: 1}},
  {$limit: 1},
  {$unset: "prob"}
])

See how it works on the playground example