$sortByCount (aggregation)

Definition

  • $sortByCount

New in version 3.4.

Groups incoming documents based on the value of a specifiedexpression, then computes the count of documents in each distinctgroup.

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

The documents are sorted by count in descending order.

The $sortByCount stage has the following prototype form:

  1. { $sortByCount:  <expression> }

FieldDescriptionexpressionExpression to group by. Youcan specify any expression except for a document literal.

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

  1. { $sortByCount:  "$employee" }

Although you cannot specify a document literal for the groupby expression, you can, however, specify a field or anexpression that evaluates to a document. For example, ifemployee and business fields are document fields,then the following $mergeObjects expression,which evaluates to a document, is a valid argument to$sortByCounts:

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

However, the following example with the document literalexpression is invalid:

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

See also

Comparison/Sort Order

Behavior

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

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

Example

Consider a collection exhibits with the following documents:

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

The following operation unwinds the tagsarray and uses the $sortByCount stage to count thenumber of documents associated with each tag:

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

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

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