Navigation

$sortByCount (aggregation)

On this page

Definition

$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:

copy 
{ $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.

copy 
{ $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:

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

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

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

See also

Comparison/Sort Order

Considerations

$sortByCount and Memory Restrictions

$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 probibit writing temporary files out to disk when allowDiskUseByDefault is set to true

See also

Aggregation Pipeline Limits

Behavior

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

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

Example

Consider a collection exhibits with the following documents:

copy 
{ "_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:

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

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

copy 
{ "_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 }

© MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.