MongoDB实现多表连接查询

最近在做结构化数据迁移,从MySQL迁移到MongoDB,一些小的细节和知识点还是挺多的

MySQL里的多表连接查询,先确定一个主表,然后通过JOIN将其它表有选择性地和主表连接起来

说实话用起来很蛋疼

比如先来个班级表

mysql root@localhost:test> select * from class;
+----+--------+---------+
| id | number | user_id |
+----+--------+---------+
| 1  | 33     | 3       |
| 2  | 11     | 1       |
| 3  | 22     | 2       |
| 5  | 55     | 5       |
+----+--------+---------+

再来个学生表,主键存在了班级表的user_id字段,作为外键

mysql root@localhost:test> select * from user;
+----+--------+-----+-----+
| id | name   | job | age |
+----+--------+-----+-----+
| 1  | lihui  | IT  | 11  |
| 2  | lilei  | IT  | 11  |
| 3  | liwei  | IT  | 11  |
| 4  | MeiMei | HR  | 14  |
+----+--------+-----+-----+

假如此时需要得到class表里现有班级里已录入信息的学生的详细信息

mysql root@localhost:test> select c.id, c.number, c.user_id, u.name, u.job, u.age from class c INNER JOIN user u ON c.user_id=u.id
+----+--------+---------+-------+-----+-----+
| id | number | user_id | name  | job | age |
+----+--------+---------+-------+-----+-----+
| 1  | 33     | 3       | liwei | IT  | 11  |
| 2  | 11     | 1       | lihui | IT  | 11  |
| 3  | 22     | 2       | lilei | IT  | 11  |
+----+--------+---------+-------+-----+-----+

外键user_id作为关联字段得到了所有人的信息

但是有一点注意,内连接INNER JOIN只返回同时存在于两张表的数据,所以class表和user表的最后一行都没返回

如果你还有其他需求,在连接过程中,想保证左表或者右表必须存在,那么可以用外连接OUTER JOIN

比如LEFT OUTER JOIN

mysql root@localhost:test> select c.id, c.number, c.user_id, u.name, u.job, u.age from class c LEFT OUTER JOIN user u ON c.user_id=u.id
+----+--------+---------+--------+--------+--------+
| id | number | user_id | name   | job    | age    |
+----+--------+---------+--------+--------+--------+
| 1  | 33     | 3       | liwei  | IT     | 11     |
| 2  | 11     | 1       | lihui  | IT     | 11     |
| 3  | 22     | 2       | lilei  | IT     | 11     |
| 5  | 55     | 5       | <null> | <null> | <null> |
+----+--------+---------+--------+--------+--------+

和INNER JOIN唯一不同的就是最后一行,主表依旧在,但user表由于没有这个user_id信息,null填充

同样的RIGHT OUTER JOIN

mysql root@localhost:test> select c.id, c.number, c.user_id, u.name, u.job, u.age from class c RIGHT OUTER JOIN user u ON c.user_id=u.id
+--------+--------+---------+--------+-----+-----+
| id     | number | user_id | name   | job | age |
+--------+--------+---------+--------+-----+-----+
| 1      | 33     | 3       | liwei  | IT  | 11  |
| 2      | 11     | 1       | lihui  | IT  | 11  |
| 3      | 22     | 2       | lilei  | IT  | 11  |
| <null> | <null> | <null>  | MeiMei | HR  | 14  |
+--------+--------+---------+--------+-----+-----+

保证user表存在,class表全部null填充

 

上面都是SQL的用法,下面是MongoDB里如何实现类似的用法

首先是collection class,student_id作为外键

// 1
{
    "_id": ObjectId("5f13a185167a9003cf76206b"),
    "class_num": 1,
    "student_id": 1
}

// 2
{
    "_id": ObjectId("5f13a18b167a9003cf76206c"),
    "class_num": 2,
    "student_id": 2
}

// 3
{
    "_id": ObjectId("5f13a18f167a9003cf76206d"),
    "class_num": 3,
    "student_id": 3
}

然后是collection student

// 1
{
    "_id": 1,
    "name": "lihui",
    "job": "IT",
    "age": 17
}

// 2
{
    "_id": 2,
    "name": "lilei",
    "job": "TEA",
    "age": 18
}

// 3
{
    "_id": 3,
    "name": "MEIMEI",
    "job": "HR",
    "age": 16
}

下面这个链接,是MongoDB官方文档$lookup的用法,完成类似JOIN的功能

https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

具体的做法如下:

db.class.aggregate([
    {
        $lookup:
            {
                from: "student",
                localField: "student_id",
                foreignField: "_id",
                as: "class_student_infos"
            }
    }
])

 

这里几个字段的含义:

from:class作为主collection,from字段表示需要链接的collection,不明白为啥命名个from

localField:主collection里的外键字段

foreignField:连接collection里对应外键的字段

as:最终连接的部分输出的字段名称

返回的结果

// 1
{
    "_id": ObjectId("5f12bef9167a9003cf762068"),
    "class_id": 3,
    "student_id": 3,
    "class_student_infos": [
        {
            "_id": 3,
            "name": "MEIMEI",
            "job": "HR",
            "age": 16
        }
    ]
}

// 2
{
    "_id": ObjectId("5f12bf1d167a9003cf762069"),
    "class_id": 2,
    "student_id": 2,
    "class_student_infos": [
        {
            "_id": 2,
            "name": "lilei",
            "job": "TEA",
            "age": 18
        }
    ]
}

// 3
{
    "_id": ObjectId("5f12bf27167a9003cf76206a"),
    "class_id": 1,
    "student_id": 1,
    "class_student_infos": [
        {
            "_id": 1,
            "name": "lihui",
            "job": "IT",
            "age": 17
        }
    ]
}

 

此时,如果和上面SQL的例子一样,加两条两个表独有的记录

比如class collection

// 1
{
    "_id": ObjectId("5f13a185167a9003cf76206b"),
    "class_num": 1,
    "student_id": 1
}

// 2
{
    "_id": ObjectId("5f13a18b167a9003cf76206c"),
    "class_num": 2,
    "student_id": 2
}

// 3
{
    "_id": ObjectId("5f13a18f167a9003cf76206d"),
    "class_num": 3,
    "student_id": 3
}

// 4
{
    "_id": ObjectId("5f13aff0167a9003cf76206e"),
    "class_num": 4,
    "student_id": 4
}

student collection

// 1
{
    "_id": 1,
    "name": "lihui",
    "job": "IT",
    "age": 17
}

// 2
{
    "_id": 2,
    "name": "lilei",
    "job": "TEA",
    "age": 18
}

// 3
{
    "_id": 3,
    "name": "MEIMEI",
    "job": "HR",
    "age": 16
}

// 4
{
    "_id": 5,
    "name": "LIWEI",
    "job": "TEA",
    "age": 15
}

再次执行上面的$lookup命令

// 1
{
    "_id": ObjectId("5f13a185167a9003cf76206b"),
    "class_num": 1,
    "student_id": 1,
    "class_student_infos": [
        {
            "_id": 1,
            "name": "lihui",
            "job": "IT",
            "age": 17
        }
    ]
}

// 2
{
    "_id": ObjectId("5f13a18b167a9003cf76206c"),
    "class_num": 2,
    "student_id": 2,
    "class_student_infos": [
        {
            "_id": 2,
            "name": "lilei",
            "job": "TEA",
            "age": 18
        }
    ]
}

// 3
{
    "_id": ObjectId("5f13a18f167a9003cf76206d"),
    "class_num": 3,
    "student_id": 3,
    "class_student_infos": [
        {
            "_id": 3,
            "name": "MEIMEI",
            "job": "HR",
            "age": 16
        }
    ]
}

// 4
{
    "_id": ObjectId("5f13aff0167a9003cf76206e"),
    "class_num": 4,
    "student_id": 4,
    "class_student_infos": [ ]
}

可以看到多了第四条document,主collection字段都在,但是连接的student信息为空,原因是外键4在collection student里找不到,因此为空,相当于SQL里的LEFT OUTER JOIN功能

发表评论