mongodb-query - 如何在 mongodb 中的两个集合之间查找匹配元素？

Question

我在 mongodb 数据库上工作，但我很少陷入一种逻辑，我如何在 mongodb 的两个集合之间找到匹配元素。

用户集合

[{
   "_id": "57cd539d168df87ae2695543",
   "userid": "3658975589",
   "name": "John Doe",
   "email": "johndoe@gmail.com",
   "number": "123654789"
}, {
   "_id": "57cd53e6168df87ae2695544",
   "userid": "789456123",
   "name": "William Rust",
   "email": "williamrust@gmail.com",
   "number": "963258741"
}]

联系人集合

[{
    "_id": "57cd2f6c3966037787ce9550",
    "contact": [{
       "id": "457899979",
       "fullname": "Abcd Hello",
       "phonenumber": "123575784565",
       "currentUserid": "123456789"
    }, {
       "id": "7994949849",
       "fullname": "Keyboard Mouse",
       "phonenumber": "23658974262",
       "currentUserid": "123456789"
    }, {
       "id": "7848848885",
       "fullname": "John Doe",
       "phonenumber": "852147852",
       "currentUserid": "123456789"
    }]
}]

所以我想从这两个集合中找到(电话号码)匹配的元素，并列出这些元素及其名称和电子邮件。

请仔细阅读我的帖子并建议我一些解决方案。

Answer 1

我猜你想做的是“聚合+查找”。像这样:

db.users.aggregate([{$lookup:
  {
        from: "contacts",
        localField: "number",
        foreignField: "phonenumber",
        as: "same"
    }
   },
   {
      $match: { "same": { $ne: [] } }
   }
])

结果你得到:

{
"_id" : "57cd539d168df87ae2695543",
"userid" : "3658975589",
"name" : "Anshuman Pattnaik",
"email" : "anshuman@gmail.com",
"number" : "7022650603",
"same" : [
    {
        "_id" : ObjectId("5b361b864aa5144b974c9733"),
        "id" : "7848848885",
        "fullname" : "Anshuman Pattnaik",
        "phonenumber" : "7022650603",
        "currentUserid" : "123456789"
    }
]
}

如果你只想显示名称和电子邮件，你必须添加 { $project: { name: 1, email:1, _id:0 }

db.users.aggregate([{$lookup:
  {
        from: "contacts",
        localField: "number",
        foreignField: "phonenumber",
        as: "same"
    }
   },
   {
      $match: { "same": { $ne: [] } }
   },
{ $project: { name: 1, email:1, _id:0 }
])

然后你会得到:{ "name": "Anshuman Pattnaik", "email": "anshuman@gmail.com"}

为此，您必须像这样更正联系人的插入:

db.contacts.insert(

[{
   "id": "457899979",    
   "fullname": "Abcd Hello",
   "phonenumber": "123575784565",
   "currentUserid": "123456789"

}, {
   "id": "7994949849",
   "fullname": "Keyboard Mouse",
   "phonenumber": "23658974262",
   "currentUserid": "123456789"

}, {
   "id": "7848848885",
   "fullname": "Anshuman Pattnaik",
   "phonenumber": "7022650603",
   "currentUserid": "123456789"

}]
)

希望它有用!

更多信息https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/