表单生成器(Form Builder)之mongodb表单数据查询——统计查询求和 - du

表单生成器(Form Builder)之mongodb表单数据查询——统计查询求和 -du 2017-04-28 00:04:38 227人浏览无得

摘要

　　上一篇笔记仅是记录了一下简单的关联查询，根据笔记中的场景：将某一车辆关联的耗损记录全部放在了一个字段当中。不知道现在中有没有这种场景，我们的应用中没有类似的场景，可能我们更关注的是某车辆的总耗损金额和总营收金额、总里程数等，因此这篇笔记

　　上一篇笔记仅是记录了一下简单的关联查询，根据笔记中的场景：将某一车辆关联的耗损记录全部放在了一个字段当中。不知道现在中有没有这种场景，我们的应用中没有类似的场景，可能我们更关注的是某车辆的总耗损金额和总营收金额、总里程数等，因此这篇笔记记录一下怎么得到这些数据。要想获取这些记录我们首先想到的就是先按照车辆ID分组之后再对指定字段求和，可能是先入为主的原因（有了上一篇笔记），我没有按照传统的思路走下去，而是想着看看能不能在上一篇笔记的基础之上得到想要的结果。

　　我们沿着这条路在分析一下，车辆相关的耗损和营收都可以放到一个字段中。也就是说我们不用分组了，车辆相关的耗损和营收记录都冗余到了车辆信息记录中，那么接下来就是对数组中的某一字段或者多个进行求和了……查了半天并没有找到类似$sum这样的聚合管道可以对数组中的字段求和，不过得益于mongoDB管道的强大，用另外一个管道间接实现了这个功能。

db.getCollection("FORMInstace").aggregate([
    {
        $match: {
            "_id": { $in: ["1","2","3"] },
            "FormItems.key": { $ne: null }
        }
    },
    {
        $addFields: {
            FormValueObj: {
                $arrayToObject: {
                    $map: {
                        input: "$FormItems",
                        as: "field",
                        in: [ "$$field.key", "$$field.value" ]
                    }
                }
            }
        }
    },
    {
      $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
    },
    {
        $project: {
            FormItems:0,
            FormValueObj:0
        }
    },
    {   
        $lookup:{
            from:"FormInstace",
            let: { tempMainRelationKey: "$_id" },
            pipeline:[
                {
                    $match: { 
                        FormId: "507048044944692000",
                        "FormItems.key": { $ne: null }                        
                    }
                },
                {
                    $addFields: {
                        FormValueObj: {
                            $arrayToObject: {
                                $map: {
                                    input: "$FormItems",
                                    as: "field",
                                    in: [ "$$field.key", "$$field.value" ]
                                }
                            }
                        }
                    }
                },
                {
                  $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                },
                {
                    $project: {
                        FormItems:0,
                        FormValueObj:0
                    }
                },
                { $match:
                     { $expr:
                            { $eq: [ "$1572493552001.id","$$tempMainRelationKey" ] }
                     }
                },
            ],
            as:"carWastage"
        }
    },
    {   
        $lookup:{
            from:"FormInstace",
            let: { tempMainRelationKey: "$_id" },
            pipeline:[
                {
                    $match: { 
                        FormId: "507048044944693000",
                        "FormItems.key": { $ne: null }                        
                    }
                },
                {
                    $addFields: {
                        FormValueObj: {
                            $arrayToObject: {
                                $map: {
                                    input: "$FormItems",
                                    as: "field",
                                    in: [ "$$field.key", "$$field.value" ]
                                }
                            }
                        }
                    }
                },
                {
                  $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                },
                {
                    $project: {
                        FormItems:0,
                        FormValueObj:0
                    }
                },
                { $match:
                     { $expr:
                            { $eq: [ "$1572493553001.id","$$tempMainRelationKey" ] }
                     }
                },
            ],
            as:"carRevenue"
        }
    },
    {
        $addFields: {
            carWastageStatistics: {
               $reduce: {
                  input: "$carWastage",
                  initialValue: {wastageMoney:0},
                  in: {
                      wastageMoney:{ $add : ["$$value.wastageMoney", "$$this.1572493552005"] }
                  }
               }
            }
        }
    },
    {
        $addFields: {
            carRevenueStatistics: {
               $reduce: {
                  input: "$carRevenue",
                  initialValue: {revenueMoney:0,mileage:0},
                  in: { 
                    revenueMoney:{$add : ["$$value.revenueMoney", "$$this.1572493553005"]},
                    mileage:{$add : ["$$value.mileage", "$$this.1572493553006"]}
                  }
               }
            }
        }
    }
]);

　　这次统计查询就查出了某些车辆的总耗损金额和总营收金额、总里程数；只不过将结果放到对象中了（方便统计一个表中的多个字段），但是现在看不到具体的结果，将代码在修改一下，看一下结果

db.getCollection("FormInstace").aggregate([
    {
        $match: {
            "_id": { $in: ["1","2","3"] },
            "FormItems.key": { $ne: null }
        }
    },
    {
        $addFields: {
            FormValueObj: {
                $arrayToObject: {
                    $map: {
                        input: "$FormItems",
                        as: "field",
                        in: [ "$$field.key", "$$field.value" ]
                    }
                }
            }
        }
    },
    {
      $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
    },
    {
        $project: {
            FormItems:0,
            FormValueObj:0
        }
    },
    {   
        $lookup:{
            from:"FormInstace",
            let: { tempMainRelationKey: "$_id" },
            pipeline:[
                {
                    $match: { 
                        FormId: "507048044944692000",
                        "FormItems.key": { $ne: null }                        
                    }
                },
                {
                    $addFields: {
                        FormValueObj: {
                            $arrayToObject: {
                                $map: {
                                    input: "$FormItems",
                                    as: "field",
                                    in: [ "$$field.key", "$$field.value" ]
                                }
                            }
                        }
                    }
                },
                {
                  $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                },
                {
                    $project: {
                        FormItems:0,
                        FormValueObj:0
                    }
                },
                { $match:
                     { $expr:
                            { $eq: [ "$1572493552001.id","$$tempMainRelationKey" ] }
                     }
                },
            ],
            as:"carWastage"
        }
    },
    {   
        $lookup:{
            from:"FormInstace",
            let: { tempMainRelationKey: "$_id" },
            pipeline:[
                {
                    $match: { 
                        FormId: "507048044944693000",
                        "FormItems.key": { $ne: null }                        
                    }
                },
                {
                    $addFields: {
                        FormValueObj: {
                            $arrayToObject: {
                                $map: {
                                    input: "$FormItems",
                                    as: "field",
                                    in: [ "$$field.key", "$$field.value" ]
                                }
                            }
                        }
                    }
                },
                {
                  $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                },
                {
                    $project: {
                        FormItems:0,
                        FormValueObj:0
                    }
                },
                { $match:
                     { $expr:
                            { $eq: [ "$1572493553001.id","$$tempMainRelationKey" ] }
                     }
                },
            ],
            as:"carRevenue"
        }
    },
    {
        $addFields: {
            carWastage_Money: {
               $reduce: {
                  input: "$carWastage",
                  initialValue: 0,
                  in: { $add : ["$$value", "$$this.1572493552005"] }
               }
            }
        }
    },
    {
        $addFields: {
            carRevenue_Money: {
               $reduce: {
                  input: "$carRevenue",
                  initialValue: 0,
                  in: { $add : ["$$value", "$$this.1572493553005"] }
               }
            }
        }
    },
    {
        $addFields: {
            carRevenue_Mileage: {
               $reduce: {
                  input: "$carRevenue",
                  initialValue: 0,
                  in: { $add : ["$$value", "$$this.1572493553006"] }
               }
            }
        }
    }
]);

　　这样就一目了然了。统计结果对不对呢？我们用传统的分组之后再求和在查询一下，将结果做一下对比（顺便记录一下，分组求和）

db.getCollection("FormInstace").aggregate([
    {
        $match: {
            "FormId":"507048044944692000",
            "FormItems.key": { $ne: null }
        }
    },
    {
        $addFields: {
            FormValueObj: {
                $arrayToObject: {
                    $map: {
                        input: "$FormItems",
                        as: "field",
                        in: [ "$$field.key", "$$field.value" ]
                    }
                }
            }
        }
    },
    {
        $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
    },
    {
        $match: {
            "1572493552001.id": { $in:["1","2","3"]}
        }
    },
    {
        $project: {
            FormItems:0,
            FormValueObj:0
        }
    },
    {
        $group:{
           _id: "$1572493552001.id",
           wastageMoney: { $sum: "$1572493552005"}
        }
    },
    { $sort : { _id: 1 } }
]);

统计指定车辆的耗损总额

db.getCollection("FormInstace").aggregate([
    {
        $match: {
            "FormId":"507048044944693000",
            "FormItems.key": { $ne: null }
        }
    },
    {
        $addFields: {
            FormValueObj: {
                $arrayToObject: {
                    $map: {
                        input: "$FormItems",
                        as: "field",
                        in: [ "$$field.key", "$$field.value" ]
                    }
                }
            }
        }
    },
    {
        $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
    },
    {
        $match: {
            "1572493553001.id": { $in:["1","2","3"]}
        }
    },
    {
        $project: {
            FormItems:0,
            FormValueObj:0
        }
    },
    {
        $group:{
           _id: "$1572493553001.id",
           wastageMoney: { $sum: "$1572493553005"},
           mileage: { $sum: "$1572493553006"}
        }
    },
    { $sort : { _id: 1 } }
]);