上一篇筆記僅是記錄了一下簡單的關聯查詢，根據筆記中的場景：將某一車輛關聯的耗損記錄全部放在了一個欄位當中，不知道現在中有沒有這種場景，我們的應用中沒有類似的場景，可能我們更關注的是某車輛的總耗損金額和總營收金額、總里程數等，因此這篇筆記記錄一下怎么得到這些資料，要想獲取這些記錄我們首先想到的就是先按照車輛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 } }]);統計指定車輛的營收總額和里程數
對比兩次查詢結果，兩次不同的統計方式的結果相同，這樣就放心了，好了就到這里了，
********************以下是修改補充部分********************
打臉了，好疼，┭┮﹏┭┮……曾記得以前使用$sum對陣列中的某一屬性求和時沒有得到預期的結果，這篇筆記寫完之后，打算在按照常規思路（$unwind、$group、$sum等）在寫一種查詢方式，但是但是但是測驗的時候發現$sum好使了，寫放一下代碼
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.wastageMoney": { $sum: "$carWastage.1572493552005"}, "carRevenueStatistics.revenueMoney": { $sum: "$carRevenue.1572493553005"}, "carRevenueStatistics.mileage": { $sum: "$carRevenue.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": { $sum: "$carWastage.1572493552005"}, "carRevenue_Money": { $sum: "$carRevenue.1572493553005"}, "carRevenue_Mileage": { $sum: "$carRevenue.1572493553006"}, } }]);在文中第二個查詢陳述句基礎上修改下面看一下對比圖，看一下改動情況
再看一下，執行結果截圖
和上面的結果一模一樣……對mongodb管道認識不夠深入，應用中有需求就是查一下，太片面了，沒有驗證的東西千萬別亂說，切記切記切記！！！特尷尬，￣□￣｜｜
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/2503.html
標籤：其它