Hello everyone,
I’m learning MongoDB and I’m practicing with a traffic database, and I’d like to know how to get the most popular radar per month (this means that for each month, the query has to show the radar with the highest number of records on it).
This is how a document looks like in the database:
{
"_id" : "20110418123336260",
"radar" : {
"mileage" : "15",
"direction" : "ascending",
"speed limit" : "180"
},
"Record" : {
"date" : "18/04/2007",
"time" : "12:33:36.260", //Hour<24 and .260 are ms
"speed" : "193"
}
}
Thanks to the help of @Pavel_Duchovny in a similar example in my last post, I got really close to the final result, but in the end I got stucked in this query, which finds for each radar his maximum number of appeareances per month:
db.traffic.aggregate([{$project: {
"_id": "$radar",
array :{
$split:["$Record.date","/"]
}
}}, {$group: {
_id: {radar: "$_id", month: {$arrayElemAt: ["$array",1]}},
count: {"$sum": 1 }
}}, {$sort: {
"_id.radar": 1,
"_id.month": 1,
"count": -1
}}]);
This is how the data looks like after this query (and as I said, it continues with the rest of the months for this radar until 12 documents (12 months), when next radar is showed)
Could please someone help me? I tried to group in the next stage and I tried to use first, $ROOT,etc properly but with no luck.
PD: Would it be possible to show also an average of the speed of the radar fines? If so, how would it be done? I imagine it would be necessary to project the Record.speed and in some group/project stage do this, but when?:
avg : {"$avg" : "$_id.Record.speed"}
Thank you in advance!!