我如何在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,获取用户对象,并将它们与注释的结果合并。看来我做错了。


当前回答

我认为,如果你需要规范化的数据表-你需要尝试一些其他的数据库解决方案。

但是我在Git上找到了MOngo的解决方案 顺便说一下,在插入代码-它有电影的名称,但没有电影的ID。

问题

你有一个演员集合和他们所做的电影数组。

您希望生成一个Movies集合,每个Movies中都包含一个actor数组。

一些示例数据

 db.actors.insert( { actor: "Richard Gere", movies: ['Pretty Woman', 'Runaway Bride', 'Chicago'] });
 db.actors.insert( { actor: "Julia Roberts", movies: ['Pretty Woman', 'Runaway Bride', 'Erin Brockovich'] });

解决方案

我们需要循环遍历Actor文档中的每个电影,并分别发出每个电影。

这里的问题是在减少阶段。我们不能从reduce阶段发出一个数组,因此必须在返回的“value”文档中构建一个Actors数组。

The code
map = function() {
  for(var i in this.movies){
    key = { movie: this.movies[i] };
    value = { actors: [ this.actor ] };
    emit(key, value);
  }
}

reduce = function(key, values) {
  actor_list = { actors: [] };
  for(var i in values) {
    actor_list.actors = values[i].actors.concat(actor_list.actors);
  }
  return actor_list;
}

注意,actor_list实际上是一个包含数组的javascript对象。还要注意map发出相同的结构。

执行以下命令执行map / reduce,将其输出到“pivot”集合并打印结果:

printjson (db.actors。mapReduce(map, reduce, "pivot")); db.pivot.find () .forEach (printjson);

以下是输出示例,请注意《风月俏佳人》和《逃跑新娘》中都有“理查德·基尔”和“茱莉亚·罗伯茨”。

{ "_id" : { "movie" : "Chicago" }, "value" : { "actors" : [ "Richard Gere" ] } }
{ "_id" : { "movie" : "Erin Brockovich" }, "value" : { "actors" : [ "Julia Roberts" ] } }
{ "_id" : { "movie" : "Pretty Woman" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }
{ "_id" : { "movie" : "Runaway Bride" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }

其他回答

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不允许连接,但是你可以使用插件来处理。检查mongo-join插件。这是最好的,我已经用过了。你可以直接使用npm安装它,就像这个npm install mongo-join。您可以通过示例查看完整的文档。

(++)非常有用的工具,当我们需要加入(N)个集合

(——)我们可以只在查询的顶层应用条件

例子

var Join = require('mongo-join').Join, mongodb = require('mongodb'), Db = mongodb.Db, Server = mongodb.Server;
db.open(function (err, Database) {
    Database.collection('Appoint', function (err, Appoints) {

        /* we can put conditions just on the top level */
        Appoints.find({_id_Doctor: id_doctor ,full_date :{ $gte: start_date },
            full_date :{ $lte: end_date }}, function (err, cursor) {
            var join = new Join(Database).on({
                field: '_id_Doctor', // <- field in Appoints document
                to: '_id',         // <- field in User doc. treated as ObjectID automatically.
                from: 'User'  // <- collection name for User doc
            }).on({
                field: '_id_Patient', // <- field in Appoints doc
                to: '_id',         // <- field in User doc. treated as ObjectID automatically.
                from: 'User'  // <- collection name for User doc
            })
            join.toArray(cursor, function (err, joinedDocs) {

                /* do what ever you want here */
                /* you can fetch the table and apply your own conditions */
                .....
                .....
                .....


                resp.status(200);
                resp.json({
                    "status": 200,
                    "message": "success",
                    "Appoints_Range": joinedDocs,


                });
                return resp;


            });

    });

不,看起来你并没有做错。MongoDB连接是“客户端”。就像你说的

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

1) Select from the collection you're interested in.
2) From that collection pull out ID's you need
3) Select from other collections
4) Decorate your original results.

它不是一个“真正的”连接,但它实际上比SQL连接有用得多,因为您不必处理“多”面连接的重复行,而是修饰最初选择的集合。

这一页上有很多废话和FUD。结果5年后,MongoDB仍然存在。

在3.2.6之前,Mongodb不像mysql那样支持join查询。下面是适合你的解决方案。

 db.getCollection('comments').aggregate([
        {$match : {pid : 444}},
        {$lookup: {from: "users",localField: "uid",foreignField: "uid",as: "userData"}},
   ])

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

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

它使用了.mapReduce()方法

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