How to query to get random document with Referenced Documents

Question

I have 3 collections(User, Credit and Status). Every User have 1 Credit data and Many Status Data like this:

Users Document

[
  {
  "_id": ObjectId("669fbce1704d41e9040cbb41"),
  "Name": "First User"
  },
  {
  "_id": ObjectId("669fbce1704d41e9040cbb42"),
  "Name": "Second User"
  },
  {
  "_id": ObjectId("669fbce1704d41e9040cbb43"),
  "Name": "Third User"
  }
]

Credit Document

[
  {
  "_id": ObjectId("669fbce1704d41e9040cbb44"),
  "userId": ObjectId("669fbce1704d41e9040cbb41"),
  "credit": 100
  },
  {
  "_id": ObjectId("669fbce1704d41e9040cbb45"),
  "userId": ObjectId("669fbce1704d41e9040cbb42"),
  "credit": 10
  },
  {
  "_id": ObjectId("669fbce1704d41e9040cbb46"),
  "userId": ObjectId("669fbce1704d41e9040cbb43"),
  "credit": 100
  }
]

Status Document

[
  {
    "_id": ObjectId("669fbce1704d41e9040cbb47"),
    "userId": ObjectId("669fbce1704d41e9040cbb41"),
    "status": "First User Status",
    "useCredit": 1
  },
  {
    "_id": ObjectId("669fbce1704d41e9040cbb48"),
    "userId": ObjectId("669fbce1704d41e9040cbb42"),
    "status": "Second User Status",
    "useCredit": 20
  },
  {
    "_id": ObjectId("669fbce1704d41e9040cbb49"),
    "userId": ObjectId("669fbce1704d41e9040cbb42"),
    "status": "Second User Status",
    "useCredit": 10
  },
  {
    "_id": ObjectId("669fbce1704d41e9040cbb4a"),
    "userId": ObjectId("669fbce1704d41e9040cbb43"),
    "status": "Third User Status",
    "useCredit": 3
  },
  {
    "_id": ObjectId("669fbdac704d41e9040cbb4c"),
    "userId": ObjectId("669fbce1704d41e9040cbb43"),
    "status": "Third User Status",
    "useCredit": 4
  },
  {
    "_id": ObjectId("669fbdac704d41e9040cbb4d"),
    "userId": ObjectId("669fbce1704d41e9040cbb43"),
    "status": "Third User Status",
    "useCredit": 2
  },
  {
    "_id": ObjectId("669fbdac704d41e9040cbb50"),
    "userId": ObjectId("669fbce1704d41e9040cbb43"),
    "status": "Third User Status",
    "useCredit": 2
  }
]

I want to get random Status with:

1. userId of Status not equal with User "_id"
2. User Credit equal or greater than useCredit of Status

Example:

If First User do query, he will get one random data from Status Document except "_id: 669fbce1704d41e9040cbb47"(because own status) and "_id: 669fbce1704d41e9040cbb48" (because, credit not enough).

I am thinking using aggregate but I don't figure out how to do it..

Answer 1

Use $lookup to chain up the Users and Credit collection. Then, in a $lookup subpipeline, perform your filtered lookup . Add a $sample at the end of the subpipeline to perform the random picking.

db.Users.aggregate([
  {
    "$match": {
      //first user
      "_id": ObjectId("669fbce1704d41e9040cbb41")
    }
  },
  {
    "$lookup": {
      "from": "Credit",
      "localField": "_id",
      "foreignField": "userId",
      "as": "creditLookup"
    }
  },
  {
    "$unwind": {
      path: "$creditLookup",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    "$lookup": {
      "from": "Status",
      "let": {
        uid: "$_id",
        c: "$creditLookup.credit"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              "$and": [
                {
                  $ne: [
                    "$$uid",
                    "$userId"
                  ]
                },
                {
                  $gte: [
                    "$$c",
                    "$useCredit"
                  ]
                }
              ]
            }
          }
        },
        {
          "$sample": {
            "size": 1
          }
        }
      ],
      "as": "randomStatus"
    }
  },
  {
    "$unwind": {
      path: "$randomStatus",
      preserveNullAndEmptyArrays: true
    }
  }
])

Mongo Playground

Answer 2

This should fulfill the purpose

db.Status.aggregate([
  {
    $match: {
      userId: { $ne: ObjectId("123") } // Replace "123" with the current user's _id
    }
  },
  {
    $lookup: {
      from: "Credit",
      localField: "userId",
      foreignField: "userId",
      as: "creditInfo"
    }
  },
  {
    $unwind: "$creditInfo"
  },
  {
    $match: {
      $expr: {
        $gte: ["$creditInfo.credit", "$useCredit"]
      }
    }
  },
  {
    $sample: { size: 1 }
  }
]);