MongoDB 之 aggregate $lookup 关联查询 进阶篇

需求：

要对 topics 问题表根据 teach_id（教师ID），tips._id（问题标签ID） 字段进行分组统计教师的答题情况生成报表，所以得根据 教师ID，标签ID 关联查询出 教师信息，标签信息 用作报表展示。

本文基于 MongoDB V3.2 聚合查询语法，V3.6 可使用更多的特性实现

topics 表数据结构：

{
    "_id" : "ffffea46-ab7d-4c19-afa4-fe37709d5300",
    "evaluation_type" : 3,
    "type" : 3,
    "content" : "一般纳税人怎么结转成本",
    "tips" : [
        {
            "_id" : 1000,
            "tip_name" : "会计基础"
        }
    ],
    "update_at" : NumberLong("1469801351305"),
    "author_id" : 10334093,
    "timestamp" : NumberLong("1469714932097"),
    "race_time" : NumberLong("1469714947334"),
    "teach_id" : 10122995,
    "last_reply_content" : "这就要看你用的是什么成本计价方法。",
    "last_reply_at" : NumberLong("1469715279859"),
    "end_type" : 2
}

从数据结构中可以知道第一步操作是：

根据 teach_id，tips._id 字段进行分组，

可得 group（分组） 语句：

$group:{
    _id:{"teacherId":"$teach_id","tipId":"$tips._id"},
    evaluationTypes:{
        $push:"$evaluation_type"
    },
    sum: {$sum: 1}
}

$push官方文档(https://docs.mongodb.com/manual/reference/operator/update/push/index.html) 

$push：可以分组之后把 evaluation_type 字段值 赋给 evaluationTypes 加入到分组之后的返回值中

$sum官方文档(https://docs.mongodb.com/manual/reference/operator/aggregation/sum/index.html)

$sum：可得到 teach_id，tips._id 都相同的记录条数

使用脚本：

db.getCollection("topics").aggregate([
        {
            $match:{
                "race_time" : {"$gte":1522512000000,"$lte":1525103999000},
                "type":3
            }
        },
        {
            $group:{
                _id:{"teacherId":"$teach_id","tipId":"$tips._id"},
                evaluationTypes:{
                    $push:"$evaluation_type"
                },
                sum: {$sum: 1}
            }
        }
    ])

查询结果：

从结果可以看出 教师：10706280 在这个标签：1002 下一共抢答了 4 条问题，以及问题的 evaluationTypes（评价类型）

一个分组作为报表的一条记录，所以我们得计算报表的总记录数（总行数）total，所以再对分好组的记录进行无条件分组（_id: null）

使用脚本：

db.getCollection("topics").aggregate([
        {
            $match:{
                "race_time" : {"$gte":1525103910000,"$lte":1525103999000},
                "type":3
            }
        },
        {
            $group:{
                _id:{"teacherId":"$teach_id","tipId":"$tips._id"},
                evaluationTypes:{
                    $push:"$evaluation_type"
                },
                sum: {$sum: 1}
            }
        },
        {
            $group:{
                _id: null,
                list:{
                    $push:"$$ROOT"
                },
                total: {$sum: 1}
            }
        }
    ])

$$ROOT 指定的是分组前的所有数据，所以 list 会把第一次分组得到的数据都带上

total 是第一次分组之后的记录数，即一共分了多少组，之后用作分页总数

查询结果：

这里的 group ä½¿ç”¨å¯å‚考：MongoDB 之 aggregate $group 巧妙运用

教师ID 以及 标签ID 等信息包含在 list 集合里，所以需要对 list 进行扁平化（$unwind）操作，可参考：MongoDB 聚合嵌入的数组（扁平化数据+管道）

使用脚本：

db.getCollection("topics").aggregate([
        {
            $match:{
                "race_time" : {"$gte":1522512000000,"$lte":1525103999000},
                "type":3
            }
        },
        {
            $group:{
                _id:{"teacherId":"$teach_id","tipId":"$tips._id"},
                evaluationTypes:{
                    $push:"$evaluation_type"
                },
                sum: {$sum: 1}
            }
        },
        {
            $group:{
                _id: null,
                list:{
                    $push:"$$ROOT"
                },
                total: {$sum: 1}
            }
        },
        {
            $unwind:"$list"
        },
        {
            $sort: {"list.sum": -1}
        },
        {
            $skip: 0
        },
        {
            $limit: 100
        }
    ])

查询结果：

扁平化 list 集合数据之后，可根据 教师ID，标签ID 关联查询（$lookup）出教师信息，标签信息

$lookup:
{
    from:"users",
    localField:"list._id.teacherId",
    foreignField: "_id",
    as: "users"
}

$lookup（官方文档--https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/index.html）

3.2版本 只能根据 localField，foreignField 单条件关联，

3.6版本中 关联查询引入 pipeline 管道支持更多更丰富的查询，对应本文会有更好的解决方案。具体使用可以查看官方文档

之后便是对数据的多次关联查询，以及扁平化操作，下面贴上

完整查询脚本：

db.getCollection("topics").aggregate([
        {
            $match:{
                "race_time" : {"$gte":1522512000000,"$lte":1525103999000},
                "type":3
            }
        },
        {
            $group:{
                _id:{"teacherId":"$teach_id","tipId":"$tips._id"},
                evaluationTypes:{
                    $push:"$evaluation_type"
                },
                sum: {$sum: 1}
            }
        },
        {
            $group:{
                _id: null,
                list:{
                    $push:"$$ROOT"
                },
                total: {$sum: 1}
            }
        },
        {
            $unwind:"$list"
        },
        {
            $sort: {"list.sum": -1}
        },
        {
            $skip: 0
        },
        {
            $limit: 100
        },
        {
            $lookup:
            {
                from:"users",
                localField:"list._id.teacherId",
                foreignField: "_id",
                as: "users"
            }
        },
        {
            $lookup:
            {
                from:"tip_content",
                localField:"list._id.tipId",
                foreignField: "_id",
                as: "childTip"
            }
        },
        {
            $lookup:
            {
                from:"tip_content",
                localField:"childTip.parent_tip_id",
                foreignField: "_id",
                as: "parentTip"
            }
        },
        {
            $unwind:"$users"
        },
        {
            $unwind:"$childTip"
        },
        {
            $unwind:"$parentTip"
        },
        {
            $project:{
                "teacherId":"$list._id.teacherId",
                "teacherName":"$users.real_name_from_nc",
                "tuid":"$users.tuid",
                "mobile":"$users.mobile",
                "tipId":"$childTip._id",
                "tipName":"$childTip.tip_name",
                "parentTipId":"$parentTip._id",
                "parentTipName":"$parentTip.tip_name",
                "sum":"$list.sum",
                "total":"$total",
                "evaluationTypes":"$list.evaluationTypes",
                "_id":0
            }
        }
    ])

查询结果：