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

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