I need to get the N top items from a query and group all another items (not in N top) in a additional element.
For example, considering a collection with the following documents:
{user: "Ana", post: "A" },
{user: "Ana", post: "B" },
{user: "Ana", post: "C" },
{user: "Ana", post: "D" },
{user: "Bruce", post: "E" },
{user: "Bruce", post: "F" },
{user: "Bruce", post: "G" },
{user: "Cami", post: "H" },
{user: "Cami", post: "I" },
{user: "John", post: "J" },
{user: "Peter", post: "K" },
{user: "Helena", post: "L" }
I expect get the 2 users that most contribute and aggregate all others in a additional output item. For example:
{user: "Ana", count: 4},
{user: "Bruce", count: 3},
{user: "All others guys", count: 5}
Now I am using the "aggregate" function:
db.MyTest.aggregate(
[
{
$group: {
"_id": "$user",
count: {
$sum: 1
}
}
},
{
$sort: {
count: -1,
userName: 1
}
}
]
);
I have no idea how treat the "All other guys" item. My function is returning the following results:
{_id: "Ana", count: 4},
{_id: "Bruce", count: 3},
{_id: "Cami", count: 2},
{_id: "John", count: 1},
{_id: "Peter", count: 1},
{_id: "Helena", count: 1}
Any idea how do this directly in mongo with a single query?
P.S.: I am using Mongo 3.2.11.
After ordering stats you can push all users stats to array, and then take required items by index from that array, and slice all other items to aggregate their stats later:
db.users.aggregate([
{$group: {_id:"$user", count:{$sum:1}}},
{$sort: {count:-1}},
// Split stats into first, second and others
{$group: {_id:1, users:{$push:{user:"$_id", count:"$count"}}}},
{$project: {
first : {$arrayElemAt: ["$users", 0]},
second: {$arrayElemAt: ["$users", 1]},
others: {$slice:["$users", 2, {$size: "$users"}]}
}
},
// Calculate count for all other guys
{$project: {
stats: [
"$first",
"$second",
{
user: "All other guys",
count: {$sum: "$others.count"}
}
]
}
},
// Bring embeded documents to top level
{$unwind: "$stats"},
{$project: { _id:0, user: "$stats.user", count: "$stats.count" }}
])
Output:
{
"user" : "Ana",
"count" : 4
},
{
"user" : "Bruce",
"count" : 3
},
{
"user" : "All other guys",
"count" : 5
}
Note: code will work even if you have zero or single user in database. But in second case you will get empty document for stats of second most postable user. Which is pretty fair.