CODEWITHSUNDEEP
mongodb
bernhardh
bernhardh

Reputation: 3309

MongoDB Group by field, count it and sort it desc

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?

Upvotes: 12

Views: 18540

Answers (3)

vivek kumar tripathi
vivek kumar tripathi

Reputation: 51

Use Sort By Count ($sortByCount) Groups incoming documents based on the value of a specified expression, then computes the count of documents in each distinct group.

db.coll.aggregate([ { $sortByCount: "$mainsubject.code" } ]

Upvotes: 1

Davis Molinari
Davis Molinari

Reputation: 761

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}

Upvotes: 1

Ankit Chaudhary
Ankit Chaudhary

Reputation: 4089

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}
  }
])

Upvotes: 10

Related Questions