Reputation: 149
Using mongo db aggregation I wrote the following NodeJs api. I got the output but not getting my expected output, so how to do this can any one help me to solve this?
app.get('/polute', function (req, res) {
Light.aggregate([
{ $match: {
CREATE_DATE: {
$lte:new Date(),
$gte: new Date(new Date().setDate(new
Date().getDate()-120)
)
}
} },
{ $group: {
_id:{
month: { $month: "$CREATE_DATE" },
year: { $year: "$CREATE_DATE" }
},
avgofozone:{$avg:"$OZONE"}
} },
{ $sort:{ "year": -1 } },
{ $project: {
year: '$_id.year',
avgofozone: '$avgofozone',
month: '$_id.month',_id:0
} }
], function (err, polute) {
console.log("naresh:" +JSON.stringify(polute));
res.json(polute);
});
});
Actual Output:
[
{ "avgofozone" : 21.07777777777778, "year" : 2018, "month" : 2 }
{ "avgofozone" : 17.8, "year" : 2018, "month" : 3 }
{ "avgofozone" : 17.8, "year" : 2018, "month" : 1 }
]
Expected Output:
[
{
"zone_type": "avgofozone",
"year": 2018,
"February": 21.07777777777778,
"March": 17.8,
"January": 17.8
}
]
Upvotes: 3
Views: 1292
Reputation: 103455
With MongoDb 3.6 and newer, you can leverage the use of $arrayToObject
operator and a $replaceRoot
pipeline to get the desired JSON output. You would need to run the following aggregate pipeline:
const monthsEnum = {
"_id": "year",
"1": "January",
"2": "February",
"3": "March",
"4": "April",
"5": "May",
"6": "June",
"7": "July",
"8": "August",
"9": "September",
"10": "October",
"11": "November",
"12": "December"
};
Light.aggregate([
{ "$match": {
"CREATE_DATE": {
"$lte": new Date(),
"$gte": new Date(new Date().setDate(new Date().getDate()-120))
}
} },
{ "$group": {
"_id": {
"month": { "$month": "$CREATE_DATE" },
"year": { "$year": "$CREATE_DATE" }
},
"avgofozone": { "$avg": "$OZONE" }
} },
{ "$group": {
"_id": "$year",
"avgs": {
"$push": {
"k": { "$substr": ["$month", 0, -1 ] },
"v": "$avgofozone"
}
}
} },
{ "$replaceRoot": {
"newRoot": {
"$mergeObjects": [
{ "$arrayToObject": "$avgs" },
"$$ROOT"
]
}
} },
{ "$project": { "avgs": 0 } }
], (err, data) => {
console.log("naresh:" +JSON.stringify(data));
const polute = Object.keys(data).reduce((p, c) => ({...p, monthsEnum[c]: data[c]}), {});
res.json(polute);
})
For reshaping using the aggregate pipeline only, you can use the $let
operator to map the month index to a value in an array. Consider running the following pipeline:
const MONTHS = [, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
Light.aggregate([
{ "$match": {
"CREATE_DATE": {
"$lte": new Date(),
"$gte": new Date(new Date().setDate(new Date().getDate()-120))
}
} },
{ "$group": {
"_id": {
"month": { "$month": "$CREATE_DATE" },
"year": { "$year": "$CREATE_DATE" }
},
"avgofozone": { "$avg": "$OZONE" }
} },
{ "$group": {
"_id": "$year",
"avgs": {
"$push": {
"k": {
"$let": {
"vars": { "monthsList": MONTHS },
"in": { "$arrayElemAt": ["$$monthsList", "$month"] }
}
},
"v": "$avgofozone"
}
}
} },
{ "$replaceRoot": {
"newRoot": {
"$mergeObjects": [
{ "$arrayToObject": "$avgs" },
"$$ROOT"
]
}
} },
{ "$project": { "avgs": 0 } }
], (err, data) => {
console.log("naresh:" +JSON.stringify(data));
res.json(data);
})
Upvotes: 1