MongoDB Group by field, count it and sort it desc

Question

I have the following document structure:

{
    ..
    "mainsubject" : {
        "code": 2768,
        "name": "Abc"
    }
}

Now I need a list of all mainsubject.code's and how often they are used.

In SQL i would do something like this:

SELECT mainsubject_code, COUNT(*) AS 'count'
FROM products
GROUP BY mainsubject_code
ORDER BY count

I already was able to group it and count it:

db.products.aggregate([
    {"$group" : {_id:"$mainsubject.code", count:{$sum:1}}}
]);

But how to sort it?

db.coll.aggregate([
  {
     $group: { 
        _id: "$mainsubject.code", 
        countA: { $sum: 1}
     }
  },
  {
    $sort:{$mainsubject.code:1}
  }
])

did not work?

Answer 1

On looking at your sql query, it looks like you want to sort by count. So in mongo query also you should mention countA as the sort field.

db.coll.aggregate([
  {
     $group: { 
        _id: "$mainsubject.code", 
        countA: { $sum: 1}
     }
  },
  {
    $sort:{'countA':1}
  }
])

Answer 2

You have to sort by _id field that is the name of the field resulting from the $group stage of your aggregation pipeline. So, modify your query in this way:

db.coll.aggregate([
  {
     $group: { 
        _id: "$mainsubject.code", 
        countA: { $sum: 1}
     }
  },
  {
     $sort:{_id:1}
  }
])

In this way you're sorting by _id ascending. Your SQL equivalent query is actually sorting by count and to achieve this you can change the $sort stage to:

$sort:{"countA":1}