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 countI 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
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
Docs Home → MongoDB Manual
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> }
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 } |