Group by specific element of array with mongo aggr

问题:

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

Such that with documents like this:

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

I could get a distinct list of top favourite foods (ie. foods at index 0) along with the height of the tallest person who's top favourite food that is?

Something like this (although it doesn't work as the array index access dot notation doesn't seem to work in the aggregation framework):

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

回答1:

Seems like you are relying on the favorite food for each person being first in the array. If so, there is an aggregation framework operator you can take advantage of.

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"
            }
        }
    }
])

On 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 these results:

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

回答2:

Looks like it isn't currently possible to extract a specific element from an array in aggregation: https://jira.mongodb.org/browse/SERVER-4589

回答3:

I think you can make use of the $project and $unwind operators (let me know if this isn't what you're trying to accomplish):

> 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/

回答4:

JUST add more information about the result 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
}

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

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