databasemongodbnosqlaggregation-frameworknosql-aggregation

MongoDB - Combining two collections without a foreign field matching


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

Solution

  • 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