How do I get comments count while fetching posts

Question

I have two collections Posts an comments. I am storing comments with postID. I want to show comments count field when fetching all the posts data. How do I achieve this?


    // posts
    {
        postID: '123',
        title: 'abc'
    }

    // comments 
    {
         postID: '123',
         comments: [
            {
                commentID: 'comment123',
                comment: 'my Comment'
            }
         ]
    }

    // Looking for this
    {
        postID: '123',
        title: 'abc',
        commentCount: 1
    }

In a single aggregation pipeline you could `"$lookup"` the `"comments"` and even use a `"pipeline"` in the `"$lookup"` to just return `"$size": "$comments"`. — rickhg12hs, Aug 25 '22 at 07:06
@rickhg12hs I can see the comments with lookup. But getting error in count here. { $lookup: { from: 'comments', localField: 'postID', foreignField: 'postID', as: 'comments', }, }, { $unwind: '$comments', }, { $project: { comments_count: { $size: '$comments' } } }, — Ben A, Aug 25 '22 at 07:40

Jatin Patel · Answer 1 · 2022-08-26T05:53:25.313

1

Try This.

pipeline = [{
    "$lookup": {
        "from": "comments",
        "let": {
            "postId": "$postId",
        },
        "pipeline": [
            {
                "$match": {
                    "$expr": {
                        "$eq": ["$postId", "$$postId"]
                    },
                }
            },
            {
                "$group": {
                    "_id": "$postId",
                    "comments_count": {"$sum": 1}
                }
            }
        ],
        "as": "comments"
    }
},
{
    "$project": {
        "_id": 0,
        "postId": 1,
        "title":1,
        "comments_count": "$comments.comments_count"
    }
}]

db.posts.aggregate(pipeline)

edited Aug 26 '22 at 05:53

answered Aug 25 '22 at 07:39

Jatin Patel

11
2

Is `"$eq": ["$postId", "$postId"]` missing a `$`? – rickhg12hs Aug 25 '22 at 08:26
When I try to use your pipeline on mongoplayground.net, an error is reported. Feel free to use the configuration from the link in [my answer](https://stackoverflow.com/a/73484222/1409374) if you want to see for yourself. Note that the OP used `"postID"` rather than `"postId"`. – rickhg12hs Aug 25 '22 at 08:41
Yes, Have edited the the query and added $$ in postId @rickhg12hs – Jatin Patel Aug 26 '22 at 05:54
I don't get the expected results when I try your pipeline on [mongoplayground.net](https://mongoplayground.net/p/VqOzKEx5D4p). – rickhg12hs Aug 26 '22 at 06:06

score 1 · Accepted Answer · answered Aug 25 '22 at 08:24

Here's one way you could do it.

db.posts.aggregate([
  {
    "$lookup": {
      "from": "comments",
      "localField": "postID",
      "foreignField": "postID",
      "pipeline": [
        {
          "$project": {
            "_id": 0,
            "commentCount": {"$size": "$comments"}
          }
        }
      ],
      "as": "commentCount"
    }
  },
  {
    "$project": {
      "_id": 0,
      "postID": 1,
      "title": 1,
      "commentCount": {"$first": "$commentCount.commentCount"}
    }
  }
])

Try it on mongoplayground.net.

How do I get comments count while fetching posts

2 Answers2