Group by specific element of array with mongo aggregation framework

aggregation-framework, mongodb

Solution

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
}

Problem

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

Original source