MongoDB nested lookup with 3 levels and append new value to the result docs

I need to retrieve the entire single object hierarchy from the database as a JSON. Actually the proposal about any other solution to achive this result would be highly appriciated. I decided to use MongoDB with its $lookup support.

So I have four collections:

0. Users

{ "_id" : "2", "name" : "john" }
{ "_id" : "1", "name" : "Doe" }

1. Posts

{"_id": "2","body": "hello", likes: []},
{"_id": "1","name": "hello 4", likes: [1,]},

2. Comments

{"_id": "c2","body": "hello 3",postId: "1",likes: [1,2]},
{"_id": "c1","body": "hello 2",postId: "1",likes: [1,2]},

3. Replies

{"_id": "r1","name": "hello 4",commentId: "c1",likes: [1]},
{"_id": "r3","name": "hello five",commentId: "c2",likes: [1,2]}

I basically need to retrieve all posts with all corresponding comments and comments.replies as part of my result . My aggregation:

  const posts = await PostModel.aggregate([
      {
        $lookup: {
          from: "comments",
          localField: "_id",
          foreignField: "postId",
          as: "comments",
        },
      },
      {
        $unwind: "$comments",
      },
      {
        $lookup: {
          from: "replies",
          localField: "comments._id",
          foreignField: "commentId",
          as: "comments.replies",
        },
      },
    ]).sort({
      createdAt: -1,
    });

MongoDb PlayGround

The result is pretty weird. Some records are ok. But comments return an object . There are also some duplications on the post with _id=”1″.

[
  {
    "_id": "1",
    "comments": {
      "_id": "c2",
      "body": "hello 3",
      "likes": [
        1,
        2
      ],
      "postId": "1",
      "replies": [
        {
          "_id": "r3",
          "commentId": "c2",
          "likes": [
            1,
            2
          ],
          "name": "hello five"
        }
      ]
    },
    "likes": [
      1
    ],
    "name": "hello 4"
  },
  {
    "_id": "1",
    "comments": {
      "_id": "c1",
      "body": "hello 2",
      "likes": [
        1,
        2
      ],
      "postId": "1",
      "replies": [
        {
          "_id": "r1",
          "commentId": "c1",
          "likes": [
            1
          ],
          "name": "hello 4"
        }
      ]
    },
    "likes": [
      1
    ],
    "name": "hello 4"
  }
]

Brief, This is my expected result.

• I want to get all posts, with all comments and replies associated with them .
• I want to append a count of likes likesCount:{$size:["likes"]} and since I have the user auth id(uid) ready I want to check if the user liked the post , comment or reply based on if isLiked: {$in:[ID(uid),"likes"]}

Answer

Since each post have multiple comments, after unwinding comments you need to group it together to form an array

Update I have updated the fetch approach a lil bit like the below.

  db.posts.aggregate([
  {
    $lookup: {
      from: "comments",
      localField: "_id",
      foreignField: "postId",
      as: "comments",
      
    },
    
  },
  {
    $unwind: "$comments",
    
  },
  {
    $lookup: {
      from: "replies",
      localField: "comments._id",
      foreignField: "commentId",
      as: "replies",
      
    },
    
  },
  {
    $unwind: "$replies",
    
  },
  {
    "$addFields": {
      "replies.countOflikes": {
        $size: {
          $ifNull: [
            "$replies.likes",
            []
          ]
        }
      },
      "replies.isLiked": {
        $cond: {
          if: {
            $eq: [
              {
                $size: {
                  $filter: {
                    input: "$replies.likes",
                    as: "item",
                    cond: {
                      $eq: [
                        "$$item",
                        1//id of the user whom you wanna check if liked the reply
                        
                      ]
                    }
                  }
                }
              },
              0
            ]
          },
          then: false,
          else: true
        }
      }
    }
  },
  {
    $group: {
      _id: "$comments._id",
      postId: {
        $first: "$_id"
      },
      body: {
        $first: "$body"
      },
      "comments": {
        $first: "$comments"
      },
      replies: {
        $push: "$replies"
      }
    }
  },
  {
    $addFields: {
      "comments.replies": "$replies"
    }
  },
  {
    $group: {
      _id: "$postId",
      body: {
        $first: "$body"
      },
      comments: {
        $push: "$comments"
      }
    }
  }
])

Summary of the change

1. Unwinded both comments and it’s replies
2. Added new fields for displaying isLiked and countOfLikes using addFields stage
3. grouped twice to reform original structure of the data(first grouped by comments then posts)

https://mongoplayground.net/p/lymCfeIIy9j