Can I get top 10 group list from MongoDb which is order by members count

Question

I have 2 collections one is for group details and other is for group_members details, group_members have group's _id stored (as FK)

Like group : -> _id,name,type,date

group_members -> _id,group_id,members_id,role,status

so how can I get the groups that have most count of memebrs in that with order by group_membrs's count (top 10 groups with more members)

Answer 1

So if the groupMembers collection contains the group ID as you describe, e.g.:

var r = [
 {_id: "M1", gid: "A", member: "Bob"}
 ,{_id: "M2", gid: "A", member: "Sally"}
 ,{_id: "M3", gid: "A", member: "Dan"}
 ,{_id: "M4", gid: "B", member: "Tess"}
 ,{_id: "M5", gid: "B", member: "George"}
 ,{_id: "M6", gid: "C", member: "P_1"}
 ,{_id: "M7", gid: "C", member: "P_2"}
 ,{_id: "M8", gid: "C", member: "P_3"}
 ,{_id: "M9", gid: "C", member: "P_4"}
 ];
 db.foo2.insert(r);

Then this is how you'd get the count in each group:

c=db.foo2.aggregate([
  {$group: {_id: "$gid", n: {$sum:1} }}
  ,{$sort: {n: -1}}
  ,{$limit: 10}
]);

If we toss in the group master collection, e.g.

var r = [
     {_id: "A", name: "GroupA"}
     ,{_id: "B", name: "GroupB"}
     ,{_id: "C", name: "GroupC"}
];
db.foo.insert(r);

Then we simply do a $lookup to "join" the data:

db.foo2.aggregate([
  {$group: {_id: "$gid", n: {$sum:1} }}
  ,{$sort: {n: -1}}
  ,{$limit: 10}
  ,{$lookup: { from: "foo", localField: "_id", foreignField: "_id", as: "X"}}
]);

Which will yield something like this:

{
"_id" : "C",
"n" : 4,
"X" : [
    {
        "_id" : "C",
        "name" : "GroupC"
    }
]
}
{
"_id" : "A",
"n" : 3,
"X" : [
    {
        "_id" : "A",
        "name" : "GroupA"
    }
]
}