Getting a complete object via $max in mongodb aggregation

Question

I have a collection of objects that need to be grouped by masterID or id if masterID is absent. Of a group of objects with the same masterID I need to find the one with the highest pos attribute ($max: "$pos"). However, I struggle to get the complete object where pos is maximized, it seems via aggregation I can only get the pos attribute, but not the whole object. Here is the sample aggregation I use, lacking the $ifNull for masterID:

> db.tst.aggregate([{ "$group": { "_id": "$masterID", "pos": { "$max": "$pos" } } }])

Sample objects would be:

> db.tst.find()
{ "_id" : ObjectId("547d6bd28e47d05a9a492e2e"), "masterID" : "master", "pos" : "453", "id" : "hallo" }
{ "_id" : ObjectId("547d6bda8e47d05a9a492e2f"), "masterID" : "master", "pos" : "147", "id" : "welt" }
{ "_id" : ObjectId("547d6be68e47d05a9a492e30"), "masterID" : "master2", "pos" : "1", "id" : "welt" }

The wanted aggregation result is:

{ "_id" : ObjectId("547d6bd28e47d05a9a492e2e"), "masterID" : "master", "pos" : "453", "id" : "hallo" }
{ "_id" : ObjectId("547d6be68e47d05a9a492e30"), "masterID" : "master2", "pos" : "1", "id" : "welt" }

Is there a way to achieve this result via aggregation, or do I need to use $push to obtain all grouped objects and implement the maximisation on pos in the Java code around the aggregation?

This question mongodb aggregation: How to return a the object with min/max instead of the value indicates that $first or $last should be used on the objects sorted by pos, but I fail to see how that returns the whole object.

wdberkeley · Accepted Answer · 2014-12-02 15:21:23Z

8

Start by sorting by pos descending. That way, the first document encountered for each masterID is the document with the highest pos. You can use $$ROOT to refer to the entire document currently being processed in the pipeline.

db.tst.aggregate([
    { "$sort" : { "pos" : -1 } },
    { "$group": { 
        "_id": { $ifNull: [ "$masterID", "$_id" ] },
        "max_doc" : { "$first" : "$$ROOT" }
    } }
])

answered Dec 2, 2014 at 15:21

wdberkeley

11.7k1 gold badge30 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barno Over a year ago

I don't understand the $$ROOT. In $$ROOT there is a whole document after $sort? In this way you can do object ?

wdberkeley Over a year ago

$$ROOT refers to the document currently being processed in the pipeline. See http://docs.mongodb.org/manual/reference/aggregation-variables/#variable.ROOT

Barno · Accepted Answer · 2014-12-03 08:06:01Z

1

If I have understood right, you could try this:

db.tst.aggregate([
    { "$sort" : { "pos" : -1 } },
    { 
    "$group": { 
        "_id": { $ifNull: [ "$masterID", "$id" ] },    
           "_Tempid":{$first:"$_id"},
           "masterID":{$first:{ $ifNull: [ "$masterID", "$id" ] }},
           "pos": { "$max": "$pos" },
           "id" : {$first:"$id"}

      }  
  },{
      $project:{
          _id:"$_Tempid",
          "masterID":1,
          "pos":1,
          id:1
      }
  }

])

result:

{
    "result" : [ 
        {
            "_id" : ObjectId("547d6be68e47d05a9a492e30"),
            "masterID" : "master2",
            "pos" : "1",
            "id" : "welt"
        }, 
        {
            "_id" : ObjectId("547d6bd28e47d05a9a492e2e"),
            "masterID" : "master",
            "pos" : "453",
            "id" : "hallo"
        }
    ],
    "ok" : 1
}

edited Dec 3, 2014 at 8:06

answered Dec 2, 2014 at 11:51

Barno

3,3415 gold badges31 silver badges60 bronze badges

1 Comment

wdberkeley Over a year ago

You need to sort by pos first, or the document from which the $first values will come is just the first doc in the group in natural order.

Collectives™ on Stack Overflow

Getting a complete object via $max in mongodb aggregation

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related