CODEWITHSUNDEEP
mongodbmongodb-query
David
David

Reputation: 8196

MongoDB Aggregation Projection

If I have a collection as follows:

db.cafe.insert({name: "Cafe1", customers: [{name: "David", foods: [{name : "cheese"}, {name: "beef"}]}, {name: "Bill", foods: [{name: "fish"}]} ]})


    db.cafe.find().pretty()
{
    "_id" : ObjectId("54f5ae58baed23b7a34fccb6"),
    "name" : "Cafe1",
    "customers" : [
        {
            "name" : "David",
            "foods" : [
                {
                    "name" : "cheese"
                },
                {
                    "name" : "beef"
                }
            ]
        },
        {
            "name" : "Bill",
            "foods" : [
                {
                    "name" : "fish"
                }
            ]
        }
    ]
}

How can I extract an array containing just the food objects for people called "David". Desired output is just the array of foods, i.e:

[{name: "cheese"}, {name: "beef"}]

I have tried an aggregation pipeline that unwinds the cafes customers, then matches on name then projects the food, e.g:

db.cafe.aggregate( [{$unwind : "$customers"}, {$match : {"customers.name": "David"}}, {$project : {"customers.foods": 1, _id : 0} 

}] ).pretty()
{
    "customers" : {
        "foods" : [
            {
                "name" : "cheese"
            },
            {
                "name" : "beef"
            }
        ]
    }
}

This seems close to the desired result, however, I'm left with the issue that the foods I want are referenced as an array under the property customers.foods. I would like the result to directly be:

[
            {
                "name" : "cheese"
            },
            {
                "name" : "beef"
            }
        ]

is there a way I can achieve the desired output?

Upvotes: 1

Views: 9822

Answers (2)

Sede
Sede

Reputation: 61235

You are doing your projection wrong.

db.cafe.aggregate( [
    { "$match" : { "customers.name": "David" }},
    { "$unwind" : "$customers" }, 

    { "$project" : { "foods": "$customers.foods", "_id": 0 }}
])

Output

{ "foods" : [ { "name" : "cheese" }, { "name" : "beef" } ] }

Upvotes: 3

wdberkeley
wdberkeley

Reputation: 11671

You can also get (something very, very close to) your desired output with a regular query:

> db.cafe.find({ "customers.name" : "David" }, { "customers.$.foods" : 1, "_id" : 0 })
{ "customers" : [ { "name" : "David", "foods" : [ { "name" : "cheese" }, { "name" : "beef" } ] } ] }

Customers will be an array containing just the first object with name : "David". You should prefer this approach to the aggregation as it's vastly more performant. You can extract the foods array in client code.

Upvotes: 1

Related Questions