Mongodb Aggregation Lookup on Nested Array Fields overwriting other fields

Question

Here are the details of my collections, I have 4 collections.

Chat,
ChatUser,
Messages,
User

Fields of these collections are,

Chat:
    _id,
    type ('dual', 'group')

ChatUser:
    _id,
    userId (Ref: Users)
    chatId (Ref: Chat)

Messages:
    _id,
    chatId (Ref: Chat)
    fromUserId (Ref: Chat)
    type: ('text', 'media')
    message

Users:
    _id,
    name,
    avatar

I wrote an aggregate function to collect all my chats below.

let chats = await Chat.aggregate([
    {$match: {_id: {$in: chatsOfUser}}},
    {
        $lookup: {
            from: "chatusers",
            let: {cId: "$_id"},
            pipeline: [
                // <-- Added Pipeline
                {
                    $match: {
                        $expr: {
                            $and: [
                                {$eq: ["$chatId", "$$cId"]},
                                {$ne: ["$userId", mongoose.Types.ObjectId(req.user._id)]},
                            ],
                        },
                    },
                },
            ],
            as: "ChatUser",
        },
    },
    {$unwind: "$ChatUser"},
    {$unwind: "$ChatUser.userId"},
    {
        $lookup: {
            from: "users",
            localField: "ChatUser.userId",
            foreignField: "_id",
            as: "Users",
        },
    },
    {$unwind: "$Users"},
    {$project: {"Users.password": 0}},
    {
        $lookup: {
            from: "messages",
            localField: "_id",
            foreignField: "chatId",
            as: "Messages",
        },
    },
    {$sort: {"Messages.createdAt": -1}},
]);

My actual result is,

{
    "chats": [
        {
            "_id": "60db388deb35c276cd023f32",
            "type": "dual",
            "ChatUser": {
                "_id": "60db388deb35c276cd023f36",
                "chatId": "60db388deb35c276cd023f32",
                "userId": "60db387feb35c276cd023f1f",
            },
            "Users": {
                "_id": "60db387feb35c276cd023f1f",
                "firstName": "Frodo",
                "lastName": "Baggins",
                "email": "[email protected]",
                "gender": "male",
            },
            "Messages": [
                {
                    "_id": "60db38f729d01c669696cb9e",
                    "message": "Hello friend",
                    "type": "text",
                    "chatId": "60db388deb35c276cd023f32",
                    "fromUserId": "60daeb5617b93e6cb968582e",// Here I want to populate the User name and avatar into a User Field
                },
                {
                    "_id": "60db38f729d01c669696cb9f",
                    "message": "Hi buddy",
                    "type": "text",
                    "chatId": "60db388deb35c276cd023f32",
                    "fromUserId": "60db387feb35c276cd023f1f", // Here I want to populate the User name and avatar into a User Field
                }
            ]
        },
    ]
}

Here I want to populate user details of 'fromUserId' in "chats.Messages.User". But If I use the following lookup, It replaces all the fields of Messages with a new User Object. Used it before sort pipeline.

{
      $lookup: {
          from: "users",
          let: { user_id: "$Messages.fromUserId" },    
          pipeline : [
              { $match: { $expr: { $eq: [ "$_id", "$$user_id" ] } }, },
              { $project : { _id:1, firstName:1 } }
          ],
          as: "Messages.User"
      }
  },

Used unwind pipeline before the above lookup,

{ "$unwind": "$Messages" },
{ "$unwind": "$Messages.fromUserId" },

But It unwinding the total chat array by the Messages. I really want to extract the fromUserId only within 'chats.Messages.User'. Is there any solution to resolve this?

Tom Slabbaert · Accepted Answer · 2021-06-30 15:29:59Z

1

There are multiple ways of solving this issue, I choose what I think is the simplest approach without changing the existing pipeline too much.

I would change the second $lookup from the message to include a nested $lookup within it to fetch the user. This way we don't need to unwind the entire document and restructure the data after that.

It would look like this:

 {
    $lookup: {
      from: "messages",
      let: {
        id: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$id",
                "$chatId"
              ]
            }
          }
        },
        {
          $lookup: {
            from: "users",
            localField: "fromUserId",
            foreignField: "_id",
            as: "User"
          }
        },
        {
          $unwind: "$User"
        },
        {
          $project: {
            "User.password": 0
          }
        },
        
      ],
      as: "Messages",
      
    },
    
  },

Full example at:

Mongo playground

answered Jun 30, 2021 at 15:29

Tom Slabbaert

22.5k10 gold badges35 silver badges45 bronze badges

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

1 Comment

Smith Dwayne Over a year ago

Perfectly worked. Thank you. Could you please tell me the other ways?

Collectives™ on Stack Overflow

Mongodb Aggregation Lookup on Nested Array Fields overwriting other fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related