Mongodb group and sort by count

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?

asked Jun 28, 2017 at 12:10

3

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

answered Jun 28, 2017 at 12:29

Ankit ChaudharyAnkit Chaudhary

3,9681 gold badge10 silver badges23 bronze badges

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}

answered Jun 28, 2017 at 12:19

Mongodb group and sort by count

Davis MolinariDavis Molinari

7311 gold badge5 silver badges20 bronze badges

1

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

answered Apr 8 at 9:28

Mongodb group and sort by count

Docs HomeMongoDB Manual

$sortByCount

Groups incoming documents based on the value of a specified expression, then computes the count of documents in each distinct group.

Each output document contains two fields: an _id field containing the distinct grouping value, and a count field containing the number of documents belonging to that grouping or category.

The documents are sorted by count in descending order.

The $sortByCount stage has the following prototype form:

{ $sortByCount: <expression> }

Field

Description

expression

Expression to group by. You can specify any expression except for a document literal.

To specify a field path, prefix the field name with a dollar sign $ and enclose it in quotes. For example, to group by the field employee, specify "$employee" as the expression.

{ $sortByCount: "$employee" }

Although you cannot specify a document literal for the group by expression, you can, however, specify a field or an expression that evaluates to a document. For example, if employee and business fields are document fields, then the following $mergeObjects expression, which evaluates to a document, is a valid argument to $sortByCount

{ $sortByCount: { $mergeObjects: [ "$employee", "$business" ] } }

However, the following example with the document literal expression is invalid:

{ $sortByCount: { lname: "$employee.last", fname: "$employee.first" } }

Tip

See also:

$sortByCount is subject to the 100 megabyte memory usage limit, but is able to write temporary files to disk if additional space is required.

Starting in MongoDB 6.0, pipeline stages that require more than 100 megabytes of memory to execute write temporary files to disk by default. In earlier verisons of MongoDB, you must pass { allowDiskUse: true } to individual find and aggregate commands to enable this behavior.

Individual find and aggregate commands may override the allowDiskUseByDefault parameter by either:

  • Using { allowDiskUse: true } to allow writing temporary files out to disk when allowDiskUseByDefault is set to false

  • Using { allowDiskUse: false } to prohibit writing temporary files out to disk when allowDiskUseByDefault is set to true

Tip

See also:

The $sortByCount stage is equivalent to the following $group + $sort sequence:

{ $group: { _id: <expression>, count: { $sum: 1 } } },
{ $sort: { count: -1 } }

Consider a collection exhibits with the following documents:

{ "_id" : 1, "title" : "The Pillars of Society", "artist" : "Grosz", "year" : 1926, "tags" : [ "painting", "satire", "Expressionism", "caricature" ] }
{ "_id" : 2, "title" : "Melancholy III", "artist" : "Munch", "year" : 1902, "tags" : [ "woodcut", "Expressionism" ] }
{ "_id" : 3, "title" : "Dancer", "artist" : "Miro", "year" : 1925, "tags" : [ "oil", "Surrealism", "painting" ] }
{ "_id" : 4, "title" : "The Great Wave off Kanagawa", "artist" : "Hokusai", "tags" : [ "woodblock", "ukiyo-e" ] }
{ "_id" : 5, "title" : "The Persistence of Memory", "artist" : "Dali", "year" : 1931, "tags" : [ "Surrealism", "painting", "oil" ] }
{ "_id" : 6, "title" : "Composition VII", "artist" : "Kandinsky", "year" : 1913, "tags" : [ "oil", "painting", "abstract" ] }
{ "_id" : 7, "title" : "The Scream", "artist" : "Munch", "year" : 1893, "tags" : [ "Expressionism", "painting", "oil" ] }
{ "_id" : 8, "title" : "Blue Flower", "artist" : "O'Keefe", "year" : 1918, "tags" : [ "abstract", "painting" ] }

The following operation unwinds the tags array and uses the $sortByCount stage to count the number of documents associated with each tag:

db.exhibits.aggregate( [ { $unwind: "$tags" }, { $sortByCount: "$tags" } ] )

The operation returns the following documents, sorted in descending order by count:

{ "_id" : "painting", "count" : 6 }
{ "_id" : "oil", "count" : 4 }
{ "_id" : "Expressionism", "count" : 3 }
{ "_id" : "Surrealism", "count" : 2 }
{ "_id" : "abstract", "count" : 2 }
{ "_id" : "woodblock", "count" : 1 }
{ "_id" : "woodcut", "count" : 1 }
{ "_id" : "ukiyo-e", "count" : 1 }
{ "_id" : "satire", "count" : 1 }
{ "_id" : "caricature", "count" : 1 }

How do I sort groups in MongoDB?

And for group sorting in MongoDB, the $group operator is used with the $sort operator. With the help of the $group and $sort operator, MongoDB can also sort the grouped data in ascending or descending order. In this post, we have provided an informative insight into the aggregate group sort functionality of MongoDB.

Can we do group by in MongoDB?

MongoDB group by is used to group data from the collection, we can achieve group by clause using aggregate function and group method in MongoDB. While using aggregate function with group by clause query operations is faster as normal query, basically aggregate function is used in multiple condition.

How do I sort an array in MongoDB aggregation?

To sort the whole array by value, or to sort by array elements that are not documents, identify the input array and specify 1 for an ascending sort or -1 for descending sort in the sortBy parameter.

Does MongoDB support sorting?

MongoDB can perform sort operations on a single-field index in ascending or descending order. In compound indexes, the sort order determines whether the index can be sorted. The sort keys must be listed in the same order as defined in the index.