Menu
How can I translate the following Sql query for Mongo?:
select a,b,sum(c) csum from coll where active=1 group by a,b order by a Is there any way to execute a group and sort query with Mongo?
368
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.
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.
To sort documents in MongoDB, you need to use sort() method. The method accepts a document containing a list of fields along with their sorting order. To specify sorting order 1 and -1 are used. 1 is used for ascending order while -1 is used for descending order.
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.
Inspired by this example on mongo's website.
GENERATE DUMMY DATA:
> db.stack.insert({a:1,b:1,c:1,active:1}) > db.stack.insert({a:1,b:1,c:2,active:0}) > db.stack.insert({a:1,b:2,c:3,active:1}) > db.stack.insert({a:1,b:2,c:2,active:0}) > db.stack.insert({a:2,b:1,c:3,active:1}) > db.stack.insert({a:2,b:1,c:10,active:1}) > db.stack.insert({a:2,b:2,c:10,active:0}) > db.stack.insert({a:2,b:2,c:5,active:1}) MONGO QUERY:
> db.stack.aggregate( ... {$match:{active:1}}, ... {$group:{_id:{a:"$a", b:"$b"}, csum:{$sum:"$c"}}}, ... {$sort:{"_id.a":1}}) RESULT:
{"result" : [ {"_id" : {"a" : 1,"b" : 2},"csum" : 3}, {"_id" : {"a" : 1,"b" : 1},"csum" : 1}, {"_id" : {"a" : 2,"b" : 2},"csum" : 5}, {"_id" : {"a" : 2,"b" : 1},"csum" : 13} ],"ok" : 1} (NOTE: I reformatted the shell result a bit so it is more readable)
146
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
