使用 $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
}

推荐答案

我们可以使用 $lookup 3.6 及更新版本中的聚合管道运算符.

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"，然后从那里使用 $unwind 聚合管道操作符.管道的下一阶段是 $redact 阶段，使用 $$KEEP 和 $$PRUNE 系统变量.然后，您可以在 $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())
)

然后$group 你的文件通过user1"和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屋！