使用$ lookup运算符的多个联接条件 [英] Multiple join conditions using the $lookup operator

问题描述

具有以下两个集合:

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

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

我想在user1和user2上加入这两个集合.

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
}

推荐答案

我们可以使用

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

我们需要使用let可选字段将字段的值分配给变量；然后，您可以在pipeline字段阶段中访问这些变量，在其中指定要在集合上运行的管道.

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.

请注意，在$match阶段，我们使用 $expr 评估查询运算符以比较字段的值.

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

计划的最后阶段是 $replaceRoot 聚合管道阶段，我们只需使用 $mergeObjects 运算符.

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
}

---

如果您使用的不是3.6+版本，则可以先使用其中一个字段输入"user1"，然后使用 $redact 阶段使用 $project 阶段重塑文档.

---

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

产生:

{
    "_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())
)

然后按"user1"和" $group user2"

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

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

产生:

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

这篇关于使用$ lookup运算符的多个联接条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！