I have a collection of documents that look like this

{
 _id : 21353456,
product : "xy",
text : "asdf",
reviews : [
{
     username : "User1",
     userID: 12
     text : "hi",
     },
     {
     username : "User2",
     userID: 123
     text : "hi1",
     }
    ]
}

users can make multiple reviews on different products, I want to retrieve all the users that have made at least 3 reviews. I want to see the number of reviews, the reviewer name and id in alphabetic order. I have tried this code but it doesn't work

db.reviews.aggregate([{ 
  $group:{ 
    "_id": "$userID", "$userName","$text" 
    "numRev":{$numRev:{}}}}, {$match:{"numRev":{$gte: 3}}}, {$sort: {"reviewerName" : 1}}]) 

CodePudding user response：

Maybe something like this:

db.collection.aggregate([
{
  $unwind: "$reviews"
},
{
  $group: {
    _id: "$reviews.username",
    userID: {
      $last: "$reviews.userID"
    },
    reviewsNum: {
      $sum: 1
    }
  }
 },
 {
   $match: {
     reviewsNum: {
       $gte: 3
     }
   }
  },
  {
    $sort: {
      _id: 1
   }
  }
])

Explained:

1. Unwind the reviews array
2. Group by username so you get the count of reviews per user
3. Match only those reviews >=3
4. Sort by _id-> the username.

playground