CODEWITHSUNDEEP
mongodb
André Gräf
André Gräf

Reputation: 45

How to get a complete document from aggregation on mongodb

I have the following data in my collection "people":

{"name" : "Anton", "age" : 22, "city" : "New York"}
{"name" : "Anton", "age" : 21, "city" : "London"}
{"name" : "Anton", "age" : 20, "city" : "Berlin"}
{"name" : "Berta", "age" : 20, "city" : "Berlin"}

I want Mongo to give me the youngest people 1 per name with all its attributes (whole document) - which is:

{"name" : "Anton", "age" : 20, "city" : "Berlin"}
{"name" : "Berta", "age" : 20, "city" : "Berlin"}

With the following query:

db.people.aggregate( [
    {
    $group:{
           _id:"$name",
           "age": {$min:"$age"},
           city : { $first: "$city" }
           }
    }
] );

Mongo will give me:

{"_id" : "Anton", "age" : 20, "city" : "New York"} // Wrong City
{"_id" : "Berta", "age" : 20, "city" : "Berlin"}

Since I am using "$first" for the city attribute, Mongo chooses the city of the first person in the group "Anton" but the city of the youngest Anton. (I am okay with the "_id" instead of "name" in the result.)

I couldn't find a solution by googling and trawling the Mongo docs for hours.

I would be glad if anybody could correct my query in order to achieve what I need.

Upvotes: 1

Views: 139

Answers (2)

metame
metame

Reputation: 2640

The $first operator is not very useful unless it follows a $sort. You could achieve your desired output by changing your aggregation pipeline to:

db.people.aggregate([
    {
        $sort: { age: 1 }
    },
    {
        $group: {
            _id:"$name",
            age: {$first:"$age"},
            city : { $first: "$city" }
        }
     }
]);

Upvotes: 1

André Gräf
André Gräf

Reputation: 45

There is a solution by sorting the data before grouping it:

db.people.aggregate( [
    { $sort : { "age" : 1 } },
    { $group:{
        _id:"$name", 
        "age": {$min:"$age"}, 
        city : { $first: "$city" }
            }
    }

] );

However sorting may not work in case of huge datasets. For now I am fine and happy if this post was helpful for someone.

Upvotes: 1

Related Questions