DEV Community

D0xzen
D0xzen

Posted on

Mongodb replace external id after $lookup

Hi, im going crazy...

I want to replace the userId inside the comments with the real user after the $lookup, i tried in many ways, i tried to group but i cant really reach what i want

This is my field inside the page collection:


"comments" : [
        {
            "user_Id" : ObjectId("aaa"),
            "content" : "aaaa",
            "rep" : [
                {
                    "user_Id" : ObjectId("bbb"),
                    "comment" : "bbbb",
                },
                {
                    "user_Id" : ObjectId("ccc"),
                    "comment" : "cccc",
                }
            ]
        },
        {
            "user_Id" : ObjectId("ddd"),
            "content" : "ddd",
            "rep" : [ ]
        }
    ]

Users collection:


"users" : [
        {
            "_id" : ObjectId("aaa"),
            "name" : "user1",
            "email" : "test1@test.com",
        },
        {
            "_id" : ObjectId("bbb"),
            "username" : "user2",
            "email" : "test2@test.com",
          }
]

What result i was looking for:


"comments" : [
        {
            "user" : {
                "_id" : ObjectId("aaa"),
                "name" : "user1",
                "email" : "test1@test.com",
                }
            "content" : "aaaa",
            "rep" : [
                {
                    "userId" : {
            "_id" : ObjectId("bbb"),
            "username" : "user2",
            "email" : "test2@test.com",
          },
                    "comment" : "bbbb",
                },
                {
                    "user" : {
                               "_id" : ObjectId("aaa"),
                               "name" : "user1",
                "email" : "test1@test.com",
                },
                    "comment" : "cccc",
                }
            ]
        },
        {
            "user" : {
            "_id" : ObjectId("bbb"),
            "username" : "user2",
            "email" : "test2@test.com",
          },
            "content" : "ddd",
            "rep" : [ ]
        }
    ]


What i did so far:


db.pages.aggregate([
    { 
        $match: { _id: ObjectId('abcbc') }
    },
    {
        $project: {
            comments: 1,
        }
    },
    {
        $lookup:
            {
                from: 'users',
                localField: 'comments.user_Id',
                foreignField: '_id',
                as: 'users'
            }
    }
]).pretty()

Right now it gives me the correct users but it give me comments with all my comments and users with all matched users how can i replace the userId with the real user object inside rep too?
If i change inside the $lookup as into 'comments.user' it'll replace everything.

Top comments (0)