Reduce the response of mongoDB query

Question

I have documents like this:

{
    "_id" : ObjectId("53340d07d6429d27e1284c77"),
    "worktypes" : [ 
        {
            "name" : "Pompas",
            "works" : [ 
                {
                    "name" : "work 1",
                    "code" : "0001"
                }
            ]
        },
        {
            "name" : "Pompas "",
            "works" : [ 
                {
                    "name" : "work 2",
                    "code" : "0002"
                }
            ]
        }
    ]
}

I did a query for get ONLY the works of one of worktype for this document, this is the query:

db.categories.find({$and: [
        { "_id": ObjectId('53340d07d6429d27e1284c77')},
        {"worktypes.name": "Pompas"}
    ]},{"worktypes.works.$":1})

But i got

    {
    "_id" : ObjectId("53340d07d6429d27e1284c77"),
    "worktypes" : [ 
        {
            "name" : "Pompas",
            "works" : [ 
                {
                    "name" : "work 1",
                    "code" : "0001"
                }
            ]
        }
    ]
}

But i only need:

"works" : [ 
                    {
                        "name" : "work 1",
                        "code" : "0001"
                    }
                ]

How can i reduce this?

It's not possible to project a subset of an array. You'll need to do it using map-reduce. Check this [question](http://stackoverflow.com/questions/3985214/mongodb-extract-only-the-selected-item-in-array) — Anand Jayabalan, Mar 27 '14 at 14:06

score 13 · Answer 1 · edited May 23 '17 at 12:23

I think Neil Lunn's answer is mostly correct, but in my opinion it needs a few tweaks to get the expected result:

Match against "worktypes.name" rather than "worktypes.works.name"
In the $group phase, use $first instead of $push to get the first element alone
Add a $project phase to just get the "works"

db.categories.aggregate([
    { "$unwind": "$worktypes" },
    { "$unwind": "$worktypes.works" },
    { "$match": {
        "worktypes.name": "Pompas"
    }},
    { "$group": {
        "_id": "$_id",
        "works": { "$first": "$worktypes.works" }
    }},
    { "$project": {"_id":0, "works":1} }
])

Output:

{
        "result" : [
                {
                        "works" : {
                                "name" : "work 1",
                                "code" : "0001"
                        }
                }
        ],
        "ok" : 1
}

Neil Lunn · Accepted Answer · 2014-03-27T15:03:31.203

You need to use the $unwind operator when working with arrays:

db.catefories.aggregate([

    // Unwind the first array
    { "$unwind": "$worktypes" },

    // Then unwind the embedded array
    { "$unwind": "$worktypes.works" },

    // Match the item you want
    { "$match": {
        "worktypes.works.name": "work 1"
    }},

    // Group to reform the array structure
    { "$group": {
        "_id": "$_id",
        "worktypes": { "$push": "$worktypes" }
    }}
])

And to get back as an array use $group after the $unwind.

score 0 · Answer 3 · answered Mar 27 '14 at 14:03

0

You can selectively remove fields from the projection like so:

db.categories.find({$and: [
    { "_id": ObjectId('53340d07d6429d27e1284c77')},
    {"worktypes.name": "Pompas"}
]},{"worktypes.works.$":1, _id:0 })

this will prevent the _id field from being projected.

answered Mar 27 '14 at 14:03

JonM

1,314
10
14

With this dont return onlye the array, get other fields too, but thanks. – colymore Mar 27 '14 at 14:07

Reduce the response of mongoDB query

3 Answers3