mongodb-多表联查与后续的数据处理

mongodb:3.6

数据

1，user表

{
    "_id" : ObjectId("5b69062240a6d80a6cece003"),
    "name" : "小明",
    "age" : 28,
    "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
    "_class" : "com.xiangpeng.bo.UserBo"
}

2，orders表

/* 1 */
{
    "_id" : ObjectId("5b69062240a6d80a6cece004"),
    "uid" : ObjectId("5b69062240a6d80a6cece003"),
    "money" : 10.0,
    "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
    "produce" : "产品1",
    "_class" : "com.xiangpeng.bo.OrderBo"
}

/* 2 */
{
    "_id" : ObjectId("5b6a5711c2eee4295c63768e"),
    "uid" : ObjectId("5b69062240a6d80a6cece003"),
    "money" : 20.0,
    "produce" : "产品2",
    "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
    "_class" : "com.xiangpeng.bo.OrderBo"
}

查询

1,mongodb的多表查询比较简单,使用$lookup关键字即可:

db.user.aggregate([{$lookup:{from:"orders",localField:"_id",foreignField:"uid",as:"orders"}}])

结果:

{
    "_id" : ObjectId("5b69062240a6d80a6cece003"),
    "name" : "小明",
    "age" : 28,
    "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
    "_class" : "com.xiangpeng.bo.UserBo",
    "orders" : [ 
        {
            "_id" : ObjectId("5b69062240a6d80a6cece004"),
            "uid" : ObjectId("5b69062240a6d80a6cece003"),
            "money" : 10.0,
            "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
            "produce" : "产品1",
            "_class" : "com.xiangpeng.bo.OrderBo"
        }, 
        {
            "_id" : ObjectId("5b6a5711c2eee4295c63768e"),
            "uid" : ObjectId("5b69062240a6d80a6cece003"),
            "money" : 20.0,
            "produce" : "产品2",
            "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
            "_class" : "com.xiangpeng.bo.OrderBo"
        }
    ]
}

参数解释:
form:需要关联的外表名,lookup进行查询后会将所有符合条件的文档封装为一个list,as参数定义这个list的名字;

数据处理

使用$unwind将数据打散:

db.user.aggregate([
        {$lookup:{from:"orders",localField:"_id",foreignField:"uid",as:"orders"},
        {$unwind:"$orders"}
])

$unwind的作用是将文档中的数组拆分为多条,拆分结果为:

/* 1 */
{
    "_id" : ObjectId("5b69062240a6d80a6cece003"),
    "name" : "小明",
    "age" : 28,
    "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
    "_class" : "com.xiangpeng.bo.UserBo",
    "orders" : {
        "_id" : ObjectId("5b69062240a6d80a6cece004"),
        "uid" : ObjectId("5b69062240a6d80a6cece003"),
        "money" : 10.0,
        "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
        "produce" : "产品1",
        "_class" : "com.xiangpeng.bo.OrderBo"
    }
}

/* 2 */
{
    "_id" : ObjectId("5b69062240a6d80a6cece003"),
    "name" : "小明",
    "age" : 28,
    "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
    "_class" : "com.xiangpeng.bo.UserBo",
    "orders" : {
        "_id" : ObjectId("5b6a5711c2eee4295c63768e"),
        "uid" : ObjectId("5b69062240a6d80a6cece003"),
        "money" : 20.0,
        "produce" : "产品2",
        "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
        "_class" : "com.xiangpeng.bo.OrderBo"
    }
}

数据过滤

现在可以对数据进行过滤,数据过滤的步骤应该尽可能提前,但如果过滤条件中也需要筛选外表条件的话就没办法放前面了,过滤在聚合中使用$match

db.user.aggregate([
    {$lookup:{from:"orders",localField:"_id",foreignField:"uid",as:"orders"}},
    {$unwind:"$orders"},
    {$match:{name:"小明","orders.produce":"产品2"}}
])

查出小明买的产品2订单
结果展示

/* 1 */
{
    "_id" : ObjectId("5b69062240a6d80a6cece003"),
    "name" : "小明",
    "age" : 28,
    "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
    "_class" : "com.xiangpeng.bo.UserBo",
    "orders" : {
        "_id" : ObjectId("5b6a5711c2eee4295c63768e"),
        "uid" : ObjectId("5b69062240a6d80a6cece003"),
        "money" : 20.0,
        "produce" : "产品2",
        "createtime" : ISODate("2018-08-07T02:38:26.601Z"),
        "_class" : "com.xiangpeng.bo.OrderBo"
    }
}

如果对字段结果有要求可以使用$project进行字段筛选:

db.user.aggregate([
    {$lookup:{from:"orders",localField:"_id",foreignField:"uid",as:"orders"}},
    {$unwind:"$orders"},
    {$match:{name:"小明","orders.produce":"产品2"}},
    {$project:{name:"$name",age:"$age",produce:"$orders.produce",money:"$orders.money"}}])

再聚合中$可以用作引用相应字段的值
结果为:

/* 1 */
{
    "_id" : ObjectId("5b69062240a6d80a6cece003"),
    "name" : "小明",
    "age" : 28,
    "produce" : "产品2",
    "money" : 20.0
}