I have 2 collections, book purchases, and referrals. They have a completely different purpose. A user can make a referral without needing a book purchase, and vice versa. However, I need to query them both. For each book purchase document, a user gains 1 point, and for each user referral document, a user gains 2 points. I tried using $lookup
but it doesn't work if the foreign collection does not have a match (i.e. Ryan doesn't exist on the Book Purchases collection so it's removed).
db.bookPurchases.aggregate([ { $group: { _id: "$userId", points: { $sum: 1 } } }, { $lookup: { from: "userReferrals", localField: "_id", foreignField: "userId", as: "referrals" } } ])
I didn't finish the above query because the result is incomplete (Ryan is missing). Is it really possible to merge these two even if there is a document without a match OR should I create a separate collection for the points (I hope I don't need to)?
Book Purchases collection (1 point)
"userId": "801879404207931443",
"userName": "Philip",
"bookId": "111101"
"userId": "801892568375361586",
"userName": "Josh",
"bookId": "211104"
User Referrals collection (2 points)
"userId": "801879404207931443",
"userName": "Philip",
"referredUserId": "692597720661229598"
"userId": "1119157325891129495",
"userName": "Ryan",
"referredUserId": "1052088956281421824"
Here's the result that I need.
"userId": "801879404207931443",
"userName": "Philip",
"points": 3
"userId": "1119157325891129495",
"userName": "Ryan",
"points": 2
"userId": "801892568375361586",
"userName": "Josh",
"points": 1
You need the $unionWith
stage before $group
to combine documents from multiple collections.
db.bookPurchases.aggregate([
{
$project: {
_id: 0,
userId: 1,
userName: 1,
point: {
$toInt: 1
}
}
},
{
$unionWith: {
coll: "userReferrals",
pipeline: [
{
$project: {
_id: 0,
userId: 1,
userName: 2,
point: {
$toInt: 2
}
}
}
]
}
},
{
$group: {
_id: "$userId",
userName: {
$first: "$userName"
},
points: {
$sum: "$point"
}
}
},
{
$sort: {
points: -1
}
},
{
$project: {
_id: 0,
userId: "$_id",
userName: 1,
points: 1
}
}
])