Combining two collections without a foreign field match in MongoDB

Question

I have 2 collections, book purchases and referrals. They have completely different purpose. A user can make a referral without the need to have 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).

i.e. 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 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 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

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

Demo @ Mongo Playground