mongodb - 从 mongo 中的嵌入式文档中多次查找相同的集合

Question

我有一个帖子和评论的嵌入式文档。其中 comments 是包含用户详细信息的嵌入式文档commentedby。用户名很可能经常更改。因此，每次访问评论时都需要查找 user 的值。

有没有一种有效的方法可以使用 mongo 查询来做到这一点。我不喜欢使用 unwind因为我会失去其他关于分组的帖子参数

{
    "PostId":"Post001",
    "Comments":[
         {"_id": "001",
          "CommentedBy":{
            "_id":"User001",
            "Name":"UserName001",
            "email":"[e-mail]"
            }
         },
         {"_id": "002",
           "CommentedBy":{
            "_id":"User002",
            "Name":"UserName002",
            "email":"[e-mail]"
            }
         },
         {"_id": "003",
          "CommentedBy":{
            "_id":"User003",
            "Name":"UserName003",
            "email":"[e-mail]"
            }
         }
    ]
}

用户文档不仅包含用户名和 ID。使用多次查找可能最终会引用User001多次。

预期结果

{
        "PostId":"Post001",
        "Comments":[
             {"_id": "001",
              "CommentedBy":{
                "_id":"User001",
                "Name":"UserName001",
                "email":"[e-mail]",
                "Group":"Marketing",
                "Location":"India"                }
             },
             {"_id": "002",
               "CommentedBy":{
                "_id":"User002",
                "Name":"UserName002",
                "email":"[e-mail]",
                "Group":"Engineering",
                "Location":"USA"         
                }
             },
             {"_id": "003",
              "CommentedBy":{
               "_id":"User002",
                "Name":"UserName002",
                "email":"[e-mail]",
                "Group":"Engineering",
                "Location":"USA"      
                }
             }
        ]
    }

UserDocument 看起来像这样

User = [
  {
  "_id":"User001",
  "Name":"UserName001",
  "email":"[e-mail]",
  "Group":"Marketing",
  "Location":"USA"      
  },
  {
  "_id":"User002",
  "Name":"UserName002",
  "email":"[e-mail]",
  "Group":"Engineering",
  "Location":"USA"      
  }
  ...
  ...
]

我试过的查询

db.getCollection('Posts').aggregate([
    {$lookup:{
           from: "User",
           localField: "Comments.CommentedBy._id",
           foreignField: "_id",
           as: "Comments.CommentedBy"
   }},
])

1. 这对于带有 n 条评论的 n 条帖子是否有效
2. mongo 在查找时会做某种优化，User002因为它需要查找两次。
3. 如果 CommentedBy 是用户列表，在这种情况下如何进行 $lookup

标签： mongodbaggregation-frameworkpymongo