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


当前回答

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

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

其他回答

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.

通过正确组合$lookup, $project和$match,您可以在多个参数上连接多个表。这是因为它们可以被链接多次。

假设我们想做以下(引用)

SELECT S.* FROM LeftTable S
LEFT JOIN RightTable R ON S.ID = R.ID AND S.MID = R.MID  
WHERE R.TIM > 0 AND S.MOB IS NOT NULL

步骤1:链接所有表

您可以根据需要查找任意数量的表。

$lookup -查询中的每个表一个

$unwind -正确地反规格化数据,否则它将被包装在数组中

Python代码. .

db.LeftTable.aggregate([
                        # connect all tables

                        {"$lookup": {
                          "from": "RightTable",
                          "localField": "ID",
                          "foreignField": "ID",
                          "as": "R"
                        }},
                        {"$unwind": "R"}
                   
                        ])

步骤2:定义所有条件

$project:在这里定义所有的条件语句,加上所有你想选择的变量。

Python代码. .

db.LeftTable.aggregate([
                        # connect all tables

                        {"$lookup": {
                          "from": "RightTable",
                          "localField": "ID",
                          "foreignField": "ID",
                          "as": "R"
                        }},
                        {"$unwind": "R"},

                        # define conditionals + variables

                        {"$project": {
                          "midEq": {"$eq": ["$MID", "$R.MID"]},
                          "ID": 1, "MOB": 1, "MID": 1
                        }}
                        ])

第三步:连接所有的条件句

$match -使用OR或AND等连接所有条件可以有很多个。

$project:取消所有的条件

完整的Python代码。

db.LeftTable.aggregate([
                        # connect all tables

                        {"$lookup": {
                          "from": "RightTable",
                          "localField": "ID",
                          "foreignField": "ID",
                          "as": "R"
                        }},
                        {"$unwind": "$R"},

                        # define conditionals + variables

                        {"$project": {
                          "midEq": {"$eq": ["$MID", "$R.MID"]},
                          "ID": 1, "MOB": 1, "MID": 1
                        }},

                        # join all conditionals

                        {"$match": {
                          "$and": [
                            {"R.TIM": {"$gt": 0}}, 
                            {"MOB": {"$exists": True}},
                            {"midEq": {"$eq": True}}
                        ]}},

                        # undefine conditionals

                        {"$project": {
                          "midEq": 0
                        }}

                        ])

几乎任何表、条件和连接的组合都可以用这种方式完成。

我们可以使用mongodb客户端控制台在几行中使用一个简单的函数合并/连接一个集合中的所有数据,现在我们可以执行所需的查询。 下面是一个完整的例子,

——作者:

db.authors.insert([
    {
        _id: 'a1',
        name: { first: 'orlando', last: 'becerra' },
        age: 27
    },
    {
        _id: 'a2',
        name: { first: 'mayra', last: 'sanchez' },
        age: 21
    }
]);

——类:

db.categories.insert([
    {
        _id: 'c1',
        name: 'sci-fi'
    },
    {
        _id: 'c2',
        name: 'romance'
    }
]);

——书

db.books.insert([
    {
        _id: 'b1',
        name: 'Groovy Book',
        category: 'c1',
        authors: ['a1']
    },
    {
        _id: 'b2',
        name: 'Java Book',
        category: 'c2',
        authors: ['a1','a2']
    },
]);

-图书借阅

db.lendings.insert([
    {
        _id: 'l1',
        book: 'b1',
        date: new Date('01/01/11'),
        lendingBy: 'jose'
    },
    {
        _id: 'l2',
        book: 'b1',
        date: new Date('02/02/12'),
        lendingBy: 'maria'
    }
]);

-神奇之处:

db.books.find().forEach(
    function (newBook) {
        newBook.category = db.categories.findOne( { "_id": newBook.category } );
        newBook.lendings = db.lendings.find( { "book": newBook._id  } ).toArray();
        newBook.authors = db.authors.find( { "_id": { $in: newBook.authors }  } ).toArray();
        db.booksReloaded.insert(newBook);
    }
);

-获取新的收集数据:

db.booksReloaded.find().pretty()

-回复:)

{
    "_id" : "b1",
    "name" : "Groovy Book",
    "category" : {
        "_id" : "c1",
        "name" : "sci-fi"
    },
    "authors" : [
        {
            "_id" : "a1",
            "name" : {
                "first" : "orlando",
                "last" : "becerra"
            },
            "age" : 27
        }
    ],
    "lendings" : [
        {
            "_id" : "l1",
            "book" : "b1",
            "date" : ISODate("2011-01-01T00:00:00Z"),
            "lendingBy" : "jose"
        },
        {
            "_id" : "l2",
            "book" : "b1",
            "date" : ISODate("2012-02-02T00:00:00Z"),
            "lendingBy" : "maria"
        }
    ]
}
{
    "_id" : "b2",
    "name" : "Java Book",
    "category" : {
        "_id" : "c2",
        "name" : "romance"
    },
    "authors" : [
        {
            "_id" : "a1",
            "name" : {
                "first" : "orlando",
                "last" : "becerra"
            },
            "age" : 27
        },
        {
            "_id" : "a2",
            "name" : {
                "first" : "mayra",
                "last" : "sanchez"
            },
            "age" : 21
        }
    ],
    "lendings" : [ ]
}

希望这句话能帮到你。

有一个很多驱动程序都支持的规范叫做DBRef。

DBRef是用于在文档之间创建引用的更正式的规范。DBRefs(通常)包括集合名称和对象id。大多数开发人员只在集合可以从一个文档更改到下一个文档时才使用DBRefs。如果您引用的集合总是相同的,那么上面概述的手动引用更有效。

摘自MongoDB文档:数据模型>数据模型参考> 数据库的引用

我们可以使用mongoDB子查询来合并两个集合。举个例子, 评论,

`db.commentss.insert([
  { uid:12345, pid:444, comment:"blah" },
  { uid:12345, pid:888, comment:"asdf" },
  { uid:99999, pid:444, comment:"qwer" }])`

用户——

db.userss.insert([
  { uid:12345, name:"john" },
  { uid:99999, name:"mia"  }])

MongoDB子查询JOIN——

`db.commentss.find().forEach(
    function (newComments) {
        newComments.userss = db.userss.find( { "uid": newComments.uid } ).toArray();
        db.newCommentUsers.insert(newComments);
    }
);`

从新生成的Collection中获取结果

db.newCommentUsers.find().pretty()

结果——

`{
    "_id" : ObjectId("5511236e29709afa03f226ef"),
    "uid" : 12345,
    "pid" : 444,
    "comment" : "blah",
    "userss" : [
        {
            "_id" : ObjectId("5511238129709afa03f226f2"),
            "uid" : 12345,
            "name" : "john"
        }
    ]
}
{
    "_id" : ObjectId("5511236e29709afa03f226f0"),
    "uid" : 12345,
    "pid" : 888,
    "comment" : "asdf",
    "userss" : [
        {
            "_id" : ObjectId("5511238129709afa03f226f2"),
            "uid" : 12345,
            "name" : "john"
        }
    ]
}
{
    "_id" : ObjectId("5511236e29709afa03f226f1"),
    "uid" : 99999,
    "pid" : 444,
    "comment" : "qwer",
    "userss" : [
        {
            "_id" : ObjectId("5511238129709afa03f226f3"),
            "uid" : 99999,
            "name" : "mia"
        }
    ]
}`

希望这能有所帮助。