Project nested embedded document in mongodb aggregate

Question

I have a nested embedded document which looks something like this. Each post has n-comments and each comment has a user detail with name and email id.

I want to project just the name of the commented user into the list

{
    "PostId":"Post001",
    "Comments":[
         {"_id": "001",
          "CommentedBy":{
            "_id":"User001",
            "Name":"UserName001",
            "email":"[email protected]"
            }
         },
         {"_id": "002",
           "CommentedBy":{
            "_id":"User002",
            "Name":"UserName002",
            "email":"[email protected]"
            }
         },
         {"_id": "003",
          "CommentedBy":{
            "_id":"User003",
            "Name":"UserName003",
            "email":"[email protected]"
            }
         }
    ]
}

And I want to transform into something that looks like this, by using mongodb's aggregation pipeline.

{
    "PostId":"Post001"
    "Comments":[
         {"_id": "001",
          "CommentedBy":"UserName001",
         },
         {"_id": "002",
           "CommentedBy": "UserName002"
         },
         {"_id": "003",
          "CommentedBy": "UserName003"
         }
    ]
}

Using mongo's projection query provides a list of CommentedBy with all the names in it. How can I achieve this using mongo's aggregate query. Is there a way to do it without using $unwind.

Query I tried and the result I got.

db.getCollection('post').aggregate([
{$project:{"Comments.CommentedBy":"$Comments.CommentedBy.Name"}}
])

{
    "_id" : ObjectId("5b98b4cc3bb8c65aeacabd78"),
    "Comments" : [ 
        {
            "CommentedBy" : [ 
                "UserName001", 
                "UserName002", 
                "UserName003"
            ]
        }, 
        {
            "CommentedBy" : [ 
                "UserName001", 
                "UserName002", 
                "UserName003"
            ]
        }, 
        {
            "CommentedBy" : [ 
                "UserName001", 
                "UserName002", 
                "UserName003"
            ]
        }
    ]
}

Ashh · Accepted Answer · 2018-09-12 08:33:57Z

18

You can try using $map aggregation and can change the keys inside by looping over Comments array.

db.collection.aggregate([
  { "$project": {
    "PostId": 1,
    "Comments": {
      "$map": {
        "input": "$Comments",
        "as": "comment",
        "in": {
          "_id": "$$comment._id",
          "CommentedBy": "$$comment.CommentedBy.Name"
        }
      }
    }
  }}
])

edited Sep 12, 2018 at 8:33

answered Sep 12, 2018 at 8:18

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

Comments

Zlatko · Accepted Answer · 2018-09-12 07:18:40Z

You can start by planing what you wanna do. E.g. you can try this:

Plan

unwind comments
project the fields you want
group this back together
(optional) cleanup

Implementation

Unwind all the comments

So the stages would be:

const unwind = {
    $unwind: '$Comments',
};

This results in duplicating - or rather, multiplicating - your documents into as many as you have comments.

Project

Now, project the commenter name/id as needed:

const project = {
    $project: {
        PostId: 1,
        CommentId: '$Comments._id',
        CommentedBy: '$Comments.commentedBy.Name',
    },
}

Now for each comment, you have a doc: { PostId, CommentId, CommentedBy }.

Group them back again.

Now you can group your comments back, group them by PostId:

const group = {
    $group: {
        _id: '$PostId',
        PostId: '$PostId',
        Comments: {
            $push: {
                _id: '$CommentId',
                CommentedBy: '$CommentedBy',
            },
        },
    },
};

You will now get your documents like this:

{
    _id: '<PostID>',
    PostId: '<PostID>',
    Comments: [
      { _id: '<CommentId>', CommentedBy: '<username>' },
    ],
}

(optional) cleanup

You will notice that you have an extra top-level _id there, you can get rid of it in another $project phase:

const cleanup = { $project: { _id: 0, ... } };

So your whole pipe is now simple:

 db.getCollection('posts')
     .aggregate([
        unwind,
        project,
        group,
        cleanup,
     ]);

I've ommited some boilerplate and I'm typing without MongoDB here, so you might want to double and tripple check the code. (You might want to do that anyway with codes from internetz though.)

Hardik Shah · Accepted Answer · 2018-09-12 07:29:23Z

unwind comments then project it and group it by PostId by pushing all comments to array.

db.getCollection('test').aggregate([
{"$unwind" : "$Comments"},
{$project:{
    "PostId":"$PostId",
    "Comments.CommentedBy":"$Comments.CommentedBy.Name", 
    "Comments._id" : "$Comments.CommentedBy._id"
  }
},
{"$group" : {
    _id: "$PostId",
    //Comments: { "$push" : {"_id" : "$Comments._id", "CommentedBy" : "$Comments.CommentedBy"}} If you want to push specifi field.
    Comments: { "$push" : "$Comments"} // Only push whole Comments object will also work.
  }
}
])

Output:

{
    "_id" : "Post001",
    "Comments" : [ 
        {
            "_id" : "User001",
            "CommentedBy" : "UserName001"
        }, 
        {
            "_id" : "User002",
            "CommentedBy" : "UserName002"
        }, 
        {
            "_id" : "User003",
            "CommentedBy" : "UserName003"
        }
    ]
}

Note:

If you want to push specific field.

Comments: { "$push" : {"_id" : "$Comments._id", "CommentedBy" : "$Comments.CommentedBy"}}

Or whole object:

Comments: { "$push" : "$Comments"}

Collectives™ on Stack Overflow

Project nested embedded document in mongodb aggregate

3 Answers 3

Comments

Plan

Implementation

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Plan

Implementation

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related