queries on users' comments in MongoDB

Question

I'm new to MongoDB and I'm trying to write some queries for website comments.

I have a collection like this:

{   
"_id": "post1"
"comments" : [ 
    {
        "user" : "goodman",
        "text" : "hello",
        "replies" : [ 
            {
                "user" : "littleboy",
                "text" : "hi"
            }
        ]
    }, 
    {
        "user" : "richpapa",
        "text" : "haha"
    }, 
    {
        "user" : "goodman",
        "text" : "how is it going?!"
    }
]
 }
 {
   "_id": "post2"
   "comments" : [ 
     {
        "user" : "katze",
        "text" : "meow!",
        "replies" : [ 
            {
                "user" : "kitten",
                "text" : "miaw miaw!"
            }, 
            {
                "user" : "littleboy",
                "text" : "mioooooo"
            }, 
            {
                "user" : "goodman",
                "text" : "meme"
            }
        ]
    }, 
    {
        "user" : "kitten",
        "text" : "ich bin katze",
        "replies" : [ 
            {
                "user" : "sally",
                "text" : "ja, du bist katze maw"
            }
        ]
    }
 ]
 }

Each post can have several comments and each comment can also have several replies. Here are what I failed to do:

first, I wanna print the name and comments of users who have replied to their own comments. Second, I wanna print the names and also the number of users who have sent more than one message on the website (both comments and replies should be considered).

Here is my try for the first part:

db.website.aggregate([{$group : {_id : "$comments.user", 
repliers: {$addToSet : "$comments.replies.user"}}}])

If I'm not mistaken this will give the name of users who have sent comments and also the name of repliers as an array. But I could not use it to gain what I was looking for.

R2D2 · Accepted Answer

To the first part:

   db.collection.aggregate([
   {
     $unwind: "$comments"
   },
    {
     $project: {
     _id: 0,
     items: {
       $filter: {
        input: "$comments.replies",
        as: "item",
         cond: {
          $eq: [
          "$$item.user",
          "$comments.user"
        ]
       }
      }
     }
    }
    },
    {
       $unwind: "$items"
     },
      {
      $project: {
      "self_repling_user": "$items.user",
      "self_repling_comment": "$items.text"
      }
     }
   ])

playground

Explained:

Convert the comments array to object.
Filter only replies where comment.user is the repling user.
unwind the array with comments with own replies
show only the self repling user and his comment

To the second part:

db.collection.aggregate([
   {
   $unwind: "$comments"
   },
   {
    $unwind: "$comments.replies"
   },
   {
    $group: {
    _id: "allusers",
    "user2": {
      $push: "$comments.user"
       },
      user1: {
        $push: "$comments.replies.user"
       }
      }
   },
    {
     $project: {
         users: {
           $concatArrays: [
             "$user1",
            "$user2"
          ]
        }
      }
    },
    {
     $unwind: "$users"
    },
    {
      $group: {
       _id: "$users",
      count: {
       $sum: 1
       }
      }
    },
    {
     $match: {
     count: {
       $gt: 1
     }
    }
  }
  ])

playground

explained:

unwind the comments & comments.recplies arrays
group to form two arrays only with the users
join the two arrays in single one
unwind the total user array
Group by user to count number of comments or replies
Filter only those that commentor reply more then once

queries on users' comments in MongoDB

Answers (2)

Related Questions

queries on users&#39; comments in MongoDB

Answers (2)

Related Questions

queries on users' comments in MongoDB