monogodb使用$lookup,$unwind,$project,$group,$push进行多表关联查询，提取指定字段，根据指定字段分组

最新推荐文章于 2024-04-14 21:49:15 发布

原创

最新推荐文章于 2024-04-14 21:49:15 发布 · 2.7k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mongodb

mongo aggregate操作使用$lookup,$unwind,$project,$group,$push操作符，执行多表连接查询，提取多表指定字段，对指定字段进行分组求和得到结果：

db.getCollection('DeviceDetailInfo_20191230').aggregate([
{$lookup:{from:'DeviceDetailInfo_20191229',localField:"deviceId",foreignField:"deviceId",as:"basicInfo"}},
{$unwind:{path: "$basicInfo",preserveNullAndEmptyArrays:false}},
{$project:{_id:false,"bareacode":"$basicInfo.areadcode","bmanufacturer":"$basicInfo.manufacturer","bcountycode":"$basicInfo.countycode",periodic_count:"$periodic_count",subDeviceNumber:"$subDeviceNumber"}},
{$group:{_id:{areacode:"bareacode",manufacturer:"$bmanufacturer",countycode:"$bcountycode"},count1:{"$sum":"$periodic_count"},count2:{"$sum":"$subDeviceNumber"}}}
]);

语句指令说明：

[
    {
        "$lookup":{   //关联查询指令
            "from":"DeviceDetailInfo_20191229",  //连接目标表
            "localField":"deviceId",  //本表属性
            "foreignField":"deviceId",   //目标表属性
            "as":"basicInfo"   //关联进来