我如何在MongoDB中执行SQL Join等效?

例如,假设你有两个集合(用户和评论),我想拉pid=444的所有评论以及每个评论的用户信息。

comments
  { uid:12345, pid:444, comment="blah" }
  { uid:12345, pid:888, comment="asdf" }
  { uid:99999, pid:444, comment="qwer" }

users
  { uid:12345, name:"john" }
  { uid:99999, name:"mia"  }

是否有一种方法可以一次性提取具有特定字段的所有评论(例如. ...find({pid:444}))和与每个评论相关的用户信息?

目前,我首先获取符合条件的注释,然后找出结果集中的所有uid,获取用户对象,并将它们与注释的结果合并。看来我做错了。


当前回答

mongodb官方网站上的这个页面恰好解决了这个问题:

https://mongodb-documentation.readthedocs.io/en/latest/ecosystem/tutorial/model-data-for-ruby-on-rails.html

When we display our list of stories, we'll need to show the name of the user who posted the story. If we were using a relational database, we could perform a join on users and stores, and get all our objects in a single query. But MongoDB does not support joins and so, at times, requires bit of denormalization. Here, this means caching the 'username' attribute. Relational purists may be feeling uneasy already, as if we were violating some universal law. But let’s bear in mind that MongoDB collections are not equivalent to relational tables; each serves a unique design objective. A normalized table provides an atomic, isolated chunk of data. A document, however, more closely represents an object as a whole. In the case of a social news site, it can be argued that a username is intrinsic to the story being posted.

其他回答

mongodb官方网站上的这个页面恰好解决了这个问题:

https://mongodb-documentation.readthedocs.io/en/latest/ecosystem/tutorial/model-data-for-ruby-on-rails.html

When we display our list of stories, we'll need to show the name of the user who posted the story. If we were using a relational database, we could perform a join on users and stores, and get all our objects in a single query. But MongoDB does not support joins and so, at times, requires bit of denormalization. Here, this means caching the 'username' attribute. Relational purists may be feeling uneasy already, as if we were violating some universal law. But let’s bear in mind that MongoDB collections are not equivalent to relational tables; each serves a unique design objective. A normalized table provides an atomic, isolated chunk of data. A document, however, more closely represents an object as a whole. In the case of a social news site, it can be argued that a username is intrinsic to the story being posted.

As others have pointed out you are trying to create a relational database from none relational database which you really don't want to do but anyways, if you have a case that you have to do this here is a solution you can use. We first do a foreach find on collection A( or in your case users) and then we get each item as an object then we use object property (in your case uid) to lookup in our second collection (in your case comments) if we can find it then we have a match and we can print or do something with it. Hope this helps you and good luck :)

db.users.find().forEach(
function (object) {
    var commonInBoth=db.comments.findOne({ "uid": object.uid} );
    if (commonInBoth != null) {
        printjson(commonInBoth) ;
        printjson(object) ;
    }else {
        // did not match so we don't care in this case
    }
});

下面是一个“join”* Actors和Movies集合的例子:

https://github.com/mongodb/cookbook/blob/master/content/patterns/pivot.txt

它使用了.mapReduce()方法

join -在面向文档的数据库中加入的替代方案

从Mongo 3.2开始,这个问题的答案大多不再正确。添加到聚合管道中的新的$lookup操作符本质上与左外连接相同:

https://docs.mongodb.org/master/reference/operator/aggregation/lookup/#pipe._S_lookup

从文档中可以看出:

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

当然,MongoDB不是一个关系数据库,开发人员正在谨慎地推荐$lookup的特定用例,但至少在3.2中,使用MongoDB进行连接是可能的。

你可以在Mongo中使用3.2版本提供的查找来连接两个集合。在您的情况下,查询将是

db.comments.aggregate({
    $lookup:{
        from:"users",
        localField:"uid",
        foreignField:"uid",
        as:"users_comments"
    }
})

或者你也可以加入关于用户,然后会有一个小的变化如下所示。

db.users.aggregate({
    $lookup:{
        from:"comments",
        localField:"uid",
        foreignField:"uid",
        as:"users_comments"
    }
})

它的工作原理与SQL中的左连接和右连接一样。