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:
- userId of Status not equal with User "_id"
- 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..
2
Answers
This should fulfill the purpose
Use
$lookup
to chain up theUsers
andCredit
collection. Then, in a$lookup
subpipeline, perform your filtered lookup . Add a$sample
at the end of the subpipeline to perform the random picking.Mongo Playground