MongoDB Aggregation Query- Rename Fields Returned from Within Embedded Documents

Question

I'm currently using aggregate operator to return documents that has an array of embedded (sub) documents. I want to rename the field name for the array and also rename fields names in the embedded documents of the array.

As an example, for projection I want to rename the array from "friends" to "buddies" and I also want to rename fields in the embedded document from "name" to "nickName". Can I do this within an aggregate operation and if so how?

Here's an example of the source document:

[
    {
        _id: ObjectID,
        name: 'Matthew',
        friends: [
            {name: 'Slim', age: '32'},
            {name: 'buba', age: '36'}
        ]
    }
]

Here's what the results should look like:

[
    {
        _id: ObjectID,
        name: 'Matthew',
        buddies: [
            {nickName: 'Chris', age: '32'},
            {nickName: 'Jim', age: '36'}
        ]
    }
]

Thanks for the help in advance.

Neil Lunn · Accepted Answer · 2014-09-10 04:56:29Z

10

There are a couple of approaches to this, but it largely depends on your MongoDB version. More recent versions from 2.6 and upwards support the $map operator which you can use in $project to do what you want:

db.friend.aggregate([
    { "$project": {
        "name": 1,
        "buddies": {
            "$map": {
                "input": "$friends",
                "as": "el",
                "in": {
                    "nickName": "$$el.name",
                    "age": "$$el.age"
                }
            }
        }
    }}
])

In prior versions you would use $unwind to work with the array elements and re-construct via $group:

db.collection.aggregate([
    { "$unwind": "$friends" },
    { "$group": {
        "_id": "$_id",
        "name": { "$first": "$name" },
        "buddies": {
            "$push": {
                "nickName": "$friends.name",
                "age": "$friends.age"
            }
        }
    }}
])

With the first form being a little more efficient as you are not de-normalizing the array content and producing more documents in the pipeline to process.

edited Sep 10, 2014 at 4:56

answered Sep 10, 2014 at 4:44

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mike Barlow - BarDev Over a year ago

I just noticed your answer after I submitted my answer. I'm currently using version 2.4, but really like the $map operator of 2.6. Your answer with the $unwind and group is simpler than my since it does not include the $project. As confirmation for others, I just tried out your query using $unwind and $group and works.

Neil Lunn Over a year ago

@BarDev faster as well since you are essentially doing another pass through documents. Also noting that when you "re-name" a field with $project and include existing fields, regardless of the order you specify the existing fields will always be listed first. This is a general optimization the pipeline does by "copying" existing content. $group however will preserve the order you specify

Mike Barlow - BarDev Over a year ago

Very cool and good to know. I'm definitely a rookie when it comes to MongoDB and still have a lot to learn. Thanks for the additional information; this provides clarification of how things works.

Mike Barlow - BarDev · Accepted Answer · 2014-09-10 05:01:28Z

I think I figured it out the following aggregate query. I would be interested in if there is a better way of doing this.

db.friends.aggregate([
    { $unwind: '$friends'},
    { $project: {
        _id: 1,
        name: 1,
        buddy: {
            nickName: '$friends.name',
            age: '$friends.age',
        }
    }},
    { $group: {        
        _id: '$_id',
        name: {'$first': '$name'},
        buddies: {"$push": "$buddy"}
    }}
]);

Below I describe the response of each operator ($unwind, $project, $group)

The $unwind would return something like this. It breaks out each Embedded Document into its own document. So where we had 1 document with 2 friends sub document, now we have two document. Both documents will have the same id and name fields but the friends sub document will be different:

{
    "_id" : ObjectId("539c80d43cb9fe99d183a5f7"),
    "name" : "Matthew",
    "friends" : {"name" : "slim", "age" : "32"}
}, {
    "_id" : : ObjectId("539c80d43cb9fe99d183a5f7"),,
    "name" : "Matthew",
    "friends" : {"name" : "buba", "age" : "36"}
}

The $project would return something like this. Here I'm creating a new Embedded Document called buddy and notice that I assigning "$friends.name" to "nickName":

{
    "_id" : ObjectId("539c80d43cb9fe99d183a5f7"),
    "name" : "Matthew",
    "buddy" : {"nickName" : "slim","age" : "32"}
}, {
    "_id" : : ObjectId("539c80d43cb9fe99d183a5f7"),,
    "name" : "Matthew",
    "friends" : {"nickName" : "buba","age" : "36"}
}

And then the $group would do something like this. Here I'm just group everything back together, but setting the array name to "buddies":

{
    "_id" : ObjectId("539c80d43cb9fe99d183a5f7"),
    "name" : "Matthew",
    "buddies" : [
        {"nickName" : "slim","age" : "32"},
        {"nickName" : "buba","age" : "36"}
    ]
}

This seems to work, but if the friends array had duplicates, this process would remove the duplicates. This can be solved if there is a unique id for each friend.

Collectives™ on Stack Overflow

MongoDB Aggregation Query- Rename Fields Returned from Within Embedded Documents

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related