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

Answer 1

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

Answer 2

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

1. 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.

2. 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 }.

3. 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>' },
    ],
}

4. (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.)