表单生成器(FormBuilder)之mongodb表单数据查询——统计查询求和du
上一篇笔记仅是记录了一下简单的关联查询，根据笔记中的场景：将某一车辆关联的耗损记录全部放在了一个字段当中。不知道现在中有没有这种场景，我们的应用中没有类似的场景，可能我们更关注的是某车辆的总耗损金额和总营收金额、总里程数等，因此这篇笔记记录一下怎么得到这些数据。要想获取这些记录我们首先想到的就是先按照车辆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 } }]);
统计指定车辆的营收总额和里程数
对比两次查询结果，两次不同的统计方式的结果相同。这样就放心了。好了就到这里了。
以上是 表单生成器(FormBuilder)之mongodb表单数据查询——统计查询求和du 的全部内容， 来源链接： utcz.com/z/531314.html
