Geek Answers Handbook

How to aggregate with a group and sort correctly

I am using Mongodb. Consider the following document:

{ uid: 1, created: ISODate("2014-05-02..."), another_col : "x" }, { uid: 1, created: ISODate("2014-05-05..."), another_col : "y" }, { uid: 2, created: ISODate("2014-05-10..."), another_col : "z" }, { uid: 3, created: ISODate("2014-05-05..."), another_col : "w" }, { uid: 1, created: ISODate("2014-05-01..."), another_col : "f" }, { uid: 2, created: ISODate("2014-05-22..."), another_col : "a" }

What I'm trying to do is simply group by uid and sort the order created in descending order so that I can get the first row for each uid.

Expected Output Example

 { uid: 1, created: ISODate("2014-05-05..."), another_col: "y" }, { uid: 2, created: ISODate("2014-05-22..."), another_col: "a" }, { uid: 3, created: ISODate("2014-05-05..."), another_col: "w" }

The best I could get was:

 db.mycollection.aggregate( {$group: {_id: "$uid", rows: {$push: { "created" : "$created" }}}}, sort { // doesnt work well } )

Can anyone lead me for the right combination of group and sorting? It just doesn't work as I expected. (note: I checked a lot of threads, but I cannot find the correct answer for my case)

+2
source share
3 answers

There are a few catches here.

When you use $group , the borders will be sorted in the order in which they were found, the initial or final stage of $sort . So, if your documents were originally in this order:

 { uid: 1, created: ISODate("2014-05-02..."), another_col : "x" }, { uid: 1, created: ISODate("2014-05-05..."), another_col : "y" }, { uid: 3, created: ISODate("2014-05-05..."), another_col : "w" }, { uid: 2, created: ISODate("2014-05-10..."), another_col : "z" },

Then just use $group without $sort at the end of the pipeline will return you the results as follows:

 { uid: 1, created: ISODate("2014-05-05..."), another_col : "y" }, { uid: 3, created: ISODate("2014-05-05..."), another_col : "w" }, { uid: 2, created: ISODate("2014-05-10..."), another_col : "z" },

This is one concept, but it really seems that you expect in the results, it requires returning the "last other fields" in the ordered uid order - this is what you are looking for. In this case, the way to get your result is first $sort , and then use $last :

 db.mycollection.aggregate([ // Sorts everything first by _id and created { "$sort": { "_id": 1, "created": 1 } }, // Group with the $last results from each boundary { "$group": { "_id": "$uid", "created": { "$last": "$created" }, "another_col": { "$last": "$created" } }} ])

Or essentially apply sorting to what you want.

The difference between $last and $max is that the latter will select the "highest" value for this field in the _id group, regardless of the current sorting in unordered order. On the other hand, $last will select the value that occurs on the same "string" as the "last" grouping _id value.

If you really were looking for sorting array values, then the approach is similar. By storing the elements of the array in the "created" order, you also sort first:

 db.mycollection.aggregate([ // Sorts everything first by _id and created { "$sort": { "_id": 1, "created": 1 } }, // Group with the $last results from each boundary { "$group": { "_id": "$uid", "row": { "$push": { "created": "$created", "another_col": "$another_col" } } }} ])

And documents with these fields will be added to the array with the order that they have already sorted.

+3
source

If all you are looking for is the first line, which means you are looking for the maximum. Just use the built-in $max battery.

 db.mycollection.aggregate([{$group: {_id: "$uid", rows: {$max:"$created"}}}])

You must use the $push battery if you need to handle all creation dates. For more information about batteries, see http://docs.mongodb.org/manual/reference/operator/aggregation/group/

From your comments, if you want to return the full documents and want to be able to sort through all the documents, you really do not need to collect the results. Something like this should get what you want.

 db.mycollection.find({$query:{}, $orderby:{uid:1,created:-1}})
0
source

using $ project along with this

 db.mycollection.aggregate([{$group: {_id: "$uid", rows: {$max:"$created"}}}])

should help you, refer to these links

http://docs.mongodb.org/manual/reference/operator/aggregation/project/

Groups and Projects of the Mongodb Project

mongodb aggregation group structure + project

0
source

More articles:


All Articles