CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Marcelo Magnani
Marcelo Magnani

Reputation: 71

Get the firsts aggregated results and aggregate all others in additional element

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.

Upvotes: 2

Views: 82

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236218

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.

Upvotes: 1

Related Questions