Geek Answers Handbook

A group for a specific array element with a mongo aggregation structure

Is it possible to use the aggregation structure to group by a specific element of an array?

For such documents:

{ name: 'Russell', favourite_foods: [ { name: 'Pizza', type: 'Four Cheeses' }, { name: 'Burger', type: 'Veggie'} ], height: 6 }

Could I get an excellent list of the best favorite foods (i.e. products with an index of 0) along with the height of the tallest person who is the best favorite food?

Something like this (although this does not work, as the notation of access points to the array index does not work in the aggregation structure):

 db.people.aggregate([ { $group : { _id: "$favourite_foods.0.name", max_height: { $max : "$height" } } } ])
+7
source share
4 answers

It looks like you are relying on your favorite food for every person who is first in the array. If so, there is an aggregation structure operator that you can use.

Here is the pipeline you can use:

 db.people.aggregate( [ { "$unwind" : "$favourite_foods" }, { "$group" : { "_id" : { "name" : "$name", "height" : "$height" }, "faveFood" : { "$first" : "$favourite_foods" } } }, { "$group" : { "_id" : "$faveFood.name", "height" : { "$max" : "$_id.height" } } } ])

In this sample dataset:

 > db.people.find().pretty() { "_id" : ObjectId("508894efd4197aa2b9490741"), "name" : "Russell", "favourite_foods" : [ { "name" : "Pizza", "type" : "Four Cheeses" }, { "name" : "Burger", "type" : "Veggie" } ], "height" : 6 } { "_id" : ObjectId("5088950bd4197aa2b9490742"), "name" : "Lucy", "favourite_foods" : [ { "name" : "Pasta", "type" : "Four Cheeses" }, { "name" : "Burger", "type" : "Veggie" } ], "height" : 5.5 } { "_id" : ObjectId("5088951dd4197aa2b9490743"), "name" : "Landy", "favourite_foods" : [ { "name" : "Pizza", "type" : "Four Cheeses" }, { "name" : "Pizza", "type" : "Veggie" } ], "height" : 5 } { "_id" : ObjectId("50889541d4197aa2b9490744"), "name" : "Augie", "favourite_foods" : [ { "name" : "Sushi", "type" : "Four Cheeses" }, { "name" : "Pizza", "type" : "Veggie" } ], "height" : 6.2 }

You get the following results:

 { "result" : [ { "_id" : "Pasta", "height" : 5.5 }, { "_id" : "Pizza", "height" : 6 }, { "_id" : "Sushi", "height" : 6.2 } ], "ok" : 1 }
+14
source

It looks like it is currently not possible to extract a specific element from an array in an aggregation: https://jira.mongodb.org/browse/SERVER-4589

+5
source

I think you can use the $ project and $ unwind statements (let me know if this is not what you are trying to do):

 > db.people.aggregate( {$unwind: "$favourite_foods"}, {$project: {food : "$favourite_foods", height: 1}}, {$group : { _id: "$food", max_height: { $max : "$height" } } }) { "result" : [ { "_id" : { "name" : "Burger", "type" : "Veggie" }, "max_height" : 6 }, { "_id" : { "name" : "Pizza", "type" : "Four Cheeses" }, "max_height" : 6 } ], "ok" : 1 }

http://docs.mongodb.org/manual/applications/aggregation/

+2
source

Just add additional result information after using "$wind" :


DOCUMENT:

 > db.people.find().pretty() { "_id" : ObjectId("508894efd4197aa2b9490741"), "name" : "Russell", "favourite_foods" : [ { "name" : "Pizza", "type" : "Four Cheeses" }, { "name" : "Burger", "type" : "Veggie" } ], "height" : 6 }, ...


AGGREAGATION:

 db.people.aggregate([{ $unwind: "$favourite_foods" }]);


RESULT:

 { "_id" : ObjectId("508894efd4197aa2b9490741"), "name" : "Russell", "favourite_foods" :{ "name" : "Pizza", "type" : "Four Cheeses" }, "height" : 6 }, { "_id" : ObjectId("508894efd4197aa2b9490741"), "name" : "Russell", "favourite_foods" : { "name" : "Burger", "type" : "Veggie" }, "height" : 6 }


Additionally:
If there are more than two array fields in one collection record, we can use the "$ project" stage to indicate the array field.

 db.people.aggregate([ { $project:{ "favourite_foods": 1 } }, { $unwind: "$favourite_foods" } ]);
0
source

More articles:


All Articles