Multiple join conditions using the $lookup operator

Question

Have the two following collections:

// collection1:
{
  user1: 1,
  user2: 2,
  percent: 0.56
}

// collection2:
{
  user1: 1,
  user2: 2,
  percent: 0.3
}

I want to join these two collections on user1 and user2.

How can I write a pipeline in order to get a result like this:

{
  user1: 1,
  user2: 2,
  percent1: 0.56,
  percent2: 0.3
}

Answer 1

We can do multiple join conditions with the $lookup aggregation pipeline operator in version 3.6 and newer.

We need to assign the fields's values to variable using the let optional field; you then access those variables in the pipeline field stages where you specify the pipeline to run on the collections.

Note that in the $match stage, we use the $expr evaluation query operator to compare the fields's value.

The last stage in the pipeline is the $replaceRoot aggregation pipeline stage where we simply merge the $lookup result with part of the $$ROOT document using the $mergeObjects operator.

db.collection2.aggregate([
       {
          $lookup: {
             from: "collection1",
             let: {
                firstUser: "$user1",
                secondUser: "$user2"
             },
             pipeline: [
                {
                   $match: {
                      $expr: {
                         $and: [
                            {
                               $eq: [
                                  "$user1",
                                  "$$firstUser"
                               ]
                            },
                            {
                               $eq: [
                                  "$user2",
                                  "$$secondUser"
                               ]
                            }
                         ]
                      }
                   }
                }
             ],
             as: "result"
          }
       },
       {
          $replaceRoot: {
             newRoot: {
                $mergeObjects:[
                   {
                      $arrayElemAt: [
                         "$result",
                         0
                      ]
                   },
                   {
                      percent1: "$$ROOT.percent1"
                   }
                ]
             }
          }
       }
    ]
)

This pipeline yields something that look like this:

{
    "_id" : ObjectId("59e1ad7d36f42d8960c06022"),
    "user1" : 1,
    "user2" : 2,
    "percent" : 0.3,
    "percent1" : 0.56
}

---

If you are not on version 3.6+, you can first join using one of your field let say "user1" then from there you unwind the array of the matching document using the $unwind aggregation pipeline operator. The next stage in the pipeline is the $redact stage where you filter out those documents where the value of "user2" from the "joined" collection and the input document are not equal using the $$KEEP and $$PRUNE system variables. You can then reshape your document in $project stage.

db.collection1.aggregate([
    { "$lookup": { 
        "from": "collection2", 
        "localField": "user1", 
        "foreignField": "user1", 
        "as": "collection2_doc"
    }}, 
    { "$unwind": "$collection2_doc" },
    { "$redact": { 
        "$cond": [
            { "$eq": [ "$user2", "$collection2_doc.user2" ] }, 
            "$$KEEP", 
            "$$PRUNE"
        ]
    }}, 
    { "$project": { 
        "user1": 1, 
        "user2": 1, 
        "percent1": "$percent", 
        "percent2": "$collection2_doc.percent"
    }}
])

which produces:

{
    "_id" : ObjectId("572daa87cc52a841bb292beb"),
    "user1" : 1,
    "user2" : 2,
    "percent1" : 0.56,
    "percent2" : 0.3
}

---

If the documents in your collections have the same structure and you find yourself performing this operation often, then you should consider to merge the two collections into one or insert the documents in those collections into a new collection.

db.collection3.insertMany(
    db.collection1.find({}, {"_id": 0})
    .toArray()
    .concat(db.collection2.find({}, {"_id": 0}).toArray())
)

Then $group your documents by "user1" and "user2"

db.collection3.aggregate([
    { "$group": {
        "_id": { "user1": "$user1", "user2": "$user2" }, 
        "percent": { "$push": "$percent" }
    }}
])

which yields:

{ "_id" : { "user1" : 1, "user2" : 2 }, "percent" : [ 0.56, 0.3 ] }