Mongodb group and sort

mongodb sort
mongodb aggregate sort
mongodb group by multiple fields
mongodb group condition
mongodb group push sort
mongodb sort array
mongodb aggregate sort limit
mongodb aggregate distinct

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?

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)

$sort (aggregation) — MongoDB Manual, Sorts all input documents and returns them to the pipeline in sorted order. of an index as long as it is not preceded by a $project , $unwind , or $group stage. Mongodb group and sort. Ask Question Asked 9 years ago. Active 1 year ago. Viewed 58k times 25. 4. How can I translate the following Sql query for Mongo?: select a,b

Using the aggregate framework, you can do the following:

db.coll.aggregate({ 
    $group: { 
        _id: "$a", 
        countA: { $sum: 1}, 
        sumC:{ $sum: "$c"}, 
    },
    $sort:{a:1}
});

However, if you have too much data you may get the following error message:

{
    "errmsg" : "exception: aggregation result exceeds maximum document size (16MB)",
    "code" : 16389,
    "ok" : 0
}

See more about SQL to Mongo translation here: http://docs.mongodb.org/manual/reference/sql-aggregation-comparison/

$group (aggregation) — MongoDB Manual, an index on the grouped field which matches the sort order. In some cases, the $group stage can use the index to quickly find the first document of each group. Name Description $accumulator: Returns the result of a user-defined accumulator function. $addToSet: Returns an array of unique expression values for each group

The mongodb aggregation API seems to have changed. Now you would do

db.coll.aggregate([
  {
     $group: { 
        _id: "$a", 
        countA: { $sum: 1}, 
        sumC:{ $sum: "$c"}, 
     }
  },
  {
    $sort:{a:1}
  }
])

Note the Array syntax for the argument to aggregate(). You'd also add things link $match, $limit etc. as elements of this array.

$sortByCount (aggregation) — MongoDB Manual, The documents are sorted by count in descending order. The $sortByCount stage is equivalent to the following $group + $sort sequence: copy. copied. MongoDB – group, count and sort example. By mkyong | Last updated: May 3, 2016. Viewed: 122,089 | +189 pv/w. Some MongoDB examples to show you how to perform group

I built up a histogram and what I did with version 2.2.2 was:

answer = db.coll.group(...)
db.histo.insert(answer)
db.histo.find().sort({ field: 1 })

At this point, if you don't need it, just db.histo.drop().

You can also avoid the variable and do:

db.histo.insert(db.coll.group(...))
db.histo.ensureIndex({ field: 1 })

MongoDB - group, count and sort example, Some MongoDB examples to show you how to perform group by, count and sort query. 1. Test Data. A whois_range collection, containing many� In MongoDB, the in-memory sorting have a limit of 100M, to perform a large sort, you need enable allowDiskUse option to write data to a temporary file for sorting. To avoid the sort exceeded memory limit error, enable the allowDiskUse option.

db.coll.group(
       {key: { a:true, b:true },
        cond: { active:1 },
        reduce: function(obj,prev) { prev.csum += obj.c; },
        initial: { csum: 0 }
        });

You can execute it in MongoDB

Perform aggregation sort in MongoDB?, You can use aggregate() method along with $sort() operator for this. To understand the concept, let us create a collection with the document. MongoDB Sort by Descending Order. One can specify the order of documents to be returned based on ascending or descending order of any key in the collection. The following example shows how this can be done. db.Employee.find().sort({Employeeid:-1}).forEach(printjson) Code Explanation:

Why doesn't MongoDB support 'sorting the result of a group'?, MongoDB can also sort the grouped data at the server-side. For example: db. orders.aggregate([ { $group: { _id: "$cust_id", count: { $sum: 1 } } }, { $match:� 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.

$unwind and $group and $sort, $unwind and $group and $sort. db.scores.aggregate( [ { $unwind: "$scores" }, { $ group: { _id: "$name", score: { $sum: "$scores" } } }, { $sort: { "score" : 1 }� Home » Golang » Golang and MongoDB » Group By and Sort in GoLang and MongoDB. Previous Next. 20 May, 2019 Categories: Golang and MongoDB. Create Database.

$sort (aggregation) — MongoDB Manual 3.4, MongoDB Manual 3.4 $sort (aggregation) If $project, $unwind, or $group occur prior to the $sort operation, $sort cannot use any indexes. 参见. 邮政编码数据� Use Indexes to Sort Query Results Facet-related aggregation stages categorize and group incoming documents. The following operation uses MongoDB’s faceting

Comments
  • Marius, Disregard my edit, I must have had something else wrong, it works as you wrote it! Sorry!
  • This doesnt work anymore: "A pipeline stage specification object must contain exactly one field."
  • @user3690202 That's because aggregate now expects an array of objects instead of a single object with multiple fields.
  • There is only group, where is sort/order by?