monogodbは$lookup,$unwind,$project,$group,$pushを使用してマルチテーブル関連クエリーを行い、指定したフィールドを抽出し、指定したフィールドに基づいてグループ化する

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"   //         basicInfo   ，               
        }
    },
    {
        "$unwind":{
            "path":"$basicInfo",  //              
            "preserveNullAndEmptyArrays":"false"  //true:               ,fasle:               
        }
    },
    {
        "$project":{   //        
            "_id":false,    //       
            "bareacode":"$basicInfo.areadcode",   //  basicInfo.areadcode      bareacode
            "bmanufacturer":"$basicInfo.manufacturer",
            "bcountycode":"$basicInfo.countycode",
            "periodic_count":"$periodic_count",
            "subDeviceNumber":"$subDeviceNumber",
            "maxTxRate":"$maxTxRate",
             "averTxRate":"$averTxRate",
             "averRxRate":"$averRxRate",
             "maxRxRate":"$maxRxRate",
             "wlanRadioPower":"$wlanRadioPower"
        }
    },
    {
        "$group":{  //                 
            "_id":{     //                 
                "areacode":"$bareacode",
                "manufacturer":"$bmanufacturer",
                "countycode":"$bcountycode"
            },
            "subList":{
                    "$push":{   //      。          
                        "maxTxRate":"$maxTxRate",
                        "averTxRate":"$averTxRate",
                        "averRxRate":"$averRxRate",
                        "maxRxRate":"$maxRxRate",
                        "wlanRadioPower":"$wlanRadioPower"
                    }
                },
            "count1":{      //         
                "$sum":"$periodic_count"
            },
            "count2":{
                "$sum":"$subDeviceNumber"
            }
        }
    }
]

spring boot 2.xを使用してmongodb集約クエリーを行い、match,group,unwind,lookup,project演算子を使用します.コード例:matchがgroupで結果を出してからlookupし、unwindが必要なフィールドの最後のprojectにターゲット結果レベルを取得し、カーソルcursorで出力します.

        Criteria criteria = new Criteria();
        MatchOperation match = Aggregation.match(criteria);
        GroupOperation group = Aggregation.group("deviceId").count().as("sum").last("PONRXPOWER")
                .as("ponRxPower").last("ONUTXPOWER").as("onuTxPower").last("actualTime")
                .as("actualTime");
        LookupOperation lookup = Aggregation.lookup("LatestDeviceInfo", "_id", "_id", "basicInfo");
        UnwindOperation unwind = Aggregation.unwind("basicInfo", "arrayIndex", true);
        Fields fields = Fields.fields("_id", "sum", "basicInfo.areacode", "basicInfo.manufacturer",
                "basicInfo.model", "basicInfo.PPPOEUser", "basicInfo.countycode",
                "basicInfo.RoleId", "basicInfo.OnuName", "basicInfo.BrasIP", "basicInfo.OltIP",
                "basicInfo.PonIP", "basicInfo.mac", "basicInfo.hardwareVersion",
                "basicInfo.firmwareVersion", "basicInfo.dpiVersion", "basicInfo.CommunityInfo",
                "basicInfo.emsName", "basicInfo.oltName", "basicInfo.userName201",
                "basicInfo.ponPort", "basicInfo.installaddr", "ponRxPower", "onuTxPower",
                "actualTime");
        ProjectionOperation project = Aggregation.project(fields);

        Aggregation aggregation = TypedAggregation.newAggregation(match, group, lookup, unwind, project);
        List pipeline = Document.parse(aggregation.toString()) .getList("pipeline", Document.class);
        MongoCursor cursor = mongoTemplate.getCollection(""collectionName").
                            aggregate(pipeline , Document.class).allowDiskUse(true)
                    .batchSize(1000).maxTime(5, TimeUnit.MINUTES).cursor();
       while (cursor.hasNext()) {
                   //      
     }