node.js - 使用 $lookup 比较两个字段的查询

Question

用户集合

[   
    {name: "alex", age: 25, surname: "brown"},
    {name: "tom", age: 34, surname: "gray"},
    {name: "alex", age: 65, surname: "red"},
    {name: "alex", age: 32, surname: "yellow"}
]

参与征集

[   
    {name: "alex", age: 32}
]

我只想获取一份名为 alex 且年龄为 32 的文档的详细信息。

到目前为止我做了什么？

db.participation.aggregate([
   {$lookup: "users" , foreignField: "name" , localField: "name", as: "document"},
   {$unwind: "document"},
   {$match: {age: "$document.age"}}
])

但是这没有返回任何内容，返回的数组是空的。

那么我应该如何创建查询？

Answer 1

执行此操作的最佳方法是在 MongoDB 3.4 中使用 $replaceRoot 管道运算符来 reshape 文档。另外，您不应该在此处使用 $unwind 。

[
   { "$match": { "name":"alex", "age": 32 } },
   {
      "$lookup": {
         "from": "user",
         "foreignField": "name",
         "localField": "name",
         "as": "result"
      }
   },
   {
      "$replaceRoot" :{
         "newRoot": {
            "$arrayElemAt": [
               {
                  "$filter": {
                     "input": "$result",
                     "cond": {
                        "$and": [
                            { "$eq": [ "$$this.age", 32 ] },
                            { "$eq": [ "$$this.name", "alex" ] }
                        ]
                     }
                  }
               },
               0
            ]
         }
      }
   }
]