1

I have a collection of feedback ratings (Communication, Timeliness & Delivery), all of these can contain 1-5 value. What i need is to count how many people rated 5 star on each ratings, then 4, then 3 then up to 1.

This is my User Schema

var UserSchema = new mongoose.Schema({
    username: String,
    fullname: String,
    email: {
        type: String,
        lowercase: true,
        unique: true
    },
  password: String,
  feedback: [{
      type: mongoose.Schema.Types.ObjectId,
      ref: 'Feedback'    
  }]
});

Feedback Schema

var FeedbackSchema = new mongoose.Schema({
    postname: String,
    userWhoSentFeedback: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'User'
    },
    message: String,
    feedbacktype: String,
    thumbsup: Boolean,
    rating: {
        delivery: Number,
        timeliness: Number,
        communication: Number
    }
});

So far i'm using $match, $unwind, $lookup and $count trying to get the value but failed. Here's the code im running...

router.get('/testagg', (req, res)=>{
    User.aggregate([
        {"$match": 
            { "username": "user1"}
        },
        {
            "$lookup": {
            "from": "feedbacks",
            "localField": "feedback",
            "foreignField": "_id",
            "as": "outputResult"
            }
        }, {"$unwind": "$outputResult"},
        {"$match": {"outputResult.rating.communication": {$eq: 1}}},{"$count": 'Communication_1'},
        {
            "$project": {
                outputResult: 1,
                Communication_1: 1
            }
        }
    ], (err, user)=>{
        console.log(user)
        res.json(user);
    })
})

with this code, this is the result i got

[
  {
    "Communication_1": 1
  }
]

So i tried to get all the rating numbers for Communication by implementing multiple $match (Which doesn't work)

router.get('/testagg', (req, res)=>{
    User.aggregate([
        {"$match": 
            { "username": "user1"}
        },
        {
            "$lookup": {
            "from": "feedbacks",
            "localField": "feedback",
            "foreignField": "_id",
            "as": "outputResult"
            }
        }, {"$unwind": "$outputResult"},
        {"$match": {"outputResult.rating.communication": {$eq: 1}}},{"$count": 'Communication_1'},
        {"$match": {"outputResult.rating.communication": {$eq: 2}}},{"$count": 'Communication_2'},
        {"$match": {"outputResult.rating.communication": {$eq: 3}}},{"$count": 'Communication_3'},
        {"$match": {"outputResult.rating.communication": {$eq: 4}}},{"$count": 'Communication_4'},
        {"$match": {"outputResult.rating.communication": {$eq: 5}}},{"$count": 'Communication_5'},
        {
            "$project": {
                outputResult: 1,
                Communication_1: 1,
                Communication_2: 1,
                Communication_3: 1,
                Communication_4: 1,
                Communication_5: 1
            }
        }
    ], (err, user)=>{
        console.log(user)
        res.json(user);
    })
})

But i got an empty response. So i think i'm doing it wrong.

Any help would be appreciated! Thank you!

**Update

I also tried this code. Just to get the communication value of 1 and 4 only.

router.get('/testagg', (req, res)=>{
    User.aggregate([
        {"$match": 
            { "username": "user1"}
        },
        {
            "$lookup": {
            "from": "feedbacks",
            "localField": "feedback",
            "foreignField": "_id",
            "as": "outputResult"
            }
        }, {"$unwind": "$outputResult"}, {
            "$project": {
                "outputResult.rating": 1,
                comm1: { $cond: [{$eq: ['$outputResult.rating.communication', 1]}, 1, 0]},
                comm4: { $cond: [{$eq: ['$outputResult.rating.communication', 4]}, 1, 0]}
            }
        },
         { $group: {
            _id: '$outputResult.rating',
            total: { $sum: 1 },
            comm1: { $sum: '$comm1'},
            comm4: { $sum: '$comm4'}
            }
        }
    ], (err, user)=>{
        console.log(user)
        res.json(user);
    })
})

And this is the result i get

[
  {
    "_id": {
      "communication": 1,
      "timeliness": 1,
      "delivery": 1
    },
    "total": 1,
    "comm1": 1,
    "comm4": 0
  },
  {
    "_id": {
      "communication": 5,
      "timeliness": 5,
      "delivery": 5
    },
    "total": 1,
    "comm1": 0,
    "comm4": 0
  },
  {
    "_id": {
      "communication": 4,
      "timeliness": 4,
      "delivery": 5
    },
    "total": 1,
    "comm1": 0,
    "comm4": 1
  }
]

Well it counts it but this is not the one i want, what i want is to have a total count of each every rating

This is the output that i want

{
"comm1" : 1,
"comm2" : 0,
"comm3" : 0,
"comm4" : 1,
"comm5" : 1
}
Improve this question

1 Answer 1

Reset to default
1

Probably the larger question here is are you actually aggregating across documents? Or are you in fact simply wanting this for a "single user"? Because that really makes a different as to how you "should" approach this. But let's address that in context of what you can do to get your results.

I honestly think you are looking at this the wrong way around. If you are going to get multiple ratings for multiple keys ( "communication", "delivery", "timeliness" ), then trying to have a "named key" for each is cumbersome and unwieldly. In fact IMHO the output like that is downright "messy".

As I see it, you are better off using natural structures for "lists" which is an "array", which you can easily iterate and access in later code.

To that end, I propose that instead you look for results that have an containing each of those "keys" and then containing it's own array for the "scores" and their "distinct counts" that you are actually after. It's a much cleaner and machine readable approach, as the alternate would mean "iterating keys" in later code, which is then really "messy".

Aggregate where you actually 'aggregate' across documents

So to pass this to the aggregation framework given the consideration that you are in fact actually "aggregating" across documents, then you should do something like this:

User.aggregate(
  [
    { "$match": { "username": "user1" }
    { "$lookup": {
      "from": "feedbacks",
      "localField": "feedback",
      "foreignField": "_id",
      "as": "feedback"
    }},
    { "$unwind": "$feedback" },
    { "$project": {
      "username": 1,
      "feedback": [
        { "k": "delivery", "v": "$feedback.rating.delivery" }, 
        { "k": "timeliness", "v": "$feedback.rating.timeliness" },
        { "k": "communication", "v": "$feedback.rating.communication" }
      ]
    }},
    { "$unwind": "$feedback" },
    { "$group": {
       "_id": {
         "username": "$username",
         "type": "$feedback.k",
         "score": "$feedback.v",
       },
       "count": { "$sum": 1 }
    }},
    { "$group": {
      "_id": { 
        "username": "$_id.username",
        "type": "$_id.type"
      },
      "scores": { 
        "$push": { 
          "score": "$_id.score",
          "count": "$count"
        }
      }
    }},
    { "$group": {
      "_id": "$_id.username",
      "feedback": {
        "$push": {
          "type": "$_id.type",
          "scores": "$scores"
        }
      }
    }}
  ],
  function(err,users) {

  }
)

The basic process here is that after the $lookup to "join", you $unwind the resulting array because you want to aggregate details out of that "across documents" anyway. The next thing we want to do is actually make your aforementioned "keys" members of an array as well. This is because to process this further we will also $unwind that content to produce effective a new document per "feedback" member as well as "per key".

The next stages are all done with $group, which are in sequence:

  1. Group and count the "distinct" feedback keys per score as per how they were formed for the supplied "username".

  2. Group on the "keys" to separate documents per "username", adding the "scores" and their distinct counts to an array.

  3. Group on just the "username" to also create an array entry per "key" containing the "scores" array as well.

Process the Cursor when you are not actually 'aggregating'

The alternate approach to this is in consideration that you are only asking for a "single username", and this data is obtained in a "single document" anyway. As such, the only thing you really want to do "on the server" is the $lookup to perform the "join". After that, then it is far more simple to process the "feedback" array withing your client code, producing the exact same distinct results.

Simply using $lookup for the join and then processing the results:

User.aggregate(
  [
    { "$match": { "username": "user1" }
    { "$lookup": {
      "from": "feedbacks",
      "localField": "feedback",
      "foreignField": "_id",
      "as": "feedback"
    }},
  ],
  function(err,users) {
    users = users.map(doc => {
      doc.feedback = [].concat.apply([],doc.feedback.map(
        r => Object.keys(r.rating).map(k => ({ k: k, v: r.rating[k] }) )
      )).reduce((a,b) => {
        if ( a.findIndex(e => JSON.stringify({ k: e.k , v: e.v }) == JSON.stringify(b) ) != -1 ) {
          a[a.findIndex(e => JSON.stringify({ k: e.k , v: e.v }) == JSON.stringify(b) )].count += 1;
        } else {
         a = a.concat([{ k: b.k, v: b.v, count: 1 }]);
        }
        return a;
      },[]).reduce((a,b) => {
        if ( a.findIndex(e => e.type == b.k) != -1 ) {
          a[a.findIndex(e => e.type == b.k)].scores.push({ score: b.v, count: b.count })
        } else {
          a = a.concat([{ type: b.k, scores: [{ score: b.v, count: b.count }] }]);
        }
        return a;
      },[]);
      return doc;
   });

   res.json(users)       
  }
)

In fact if it is a "single" user, then you probably wan't res.json(users[0]) at the end since the results of .aggregate() are always an array regardless of how many results were returned.

This is really just using .map() and .reduce() JavaScript functions over the "feedback" array in order to reshape and return the "distinct counts". There are the same sort of methods applied, but if this is indeed a single document response or even a small response with no actual need to "aggregate across documents", then it's the cleaner and likely "faster" way to process.

We could in theory write a "very complicated" version of an aggregation pipeline that did exactly the same steps for single documents as shown in the code here. However, it is "really complicated" and relies on modern methods, for what really is no significant "reduction" is the data transferred from the server.

So if you need this "across documents" then use the full aggregation pipeline in the first listing. But if you only need a "single user" at a time, or the "distinct results" per user on a small selection, then the client processing code is the way to go.

Both produce the same output, which as I mention is far friendlier than the direction you were going, and definitely easier to process in further code as needed:

{
        "_id" : "user 1",
        "feedback" : [
                {
                        "type" : "communication",
                        "scores" : [
                                {
                                        "score" : 2,
                                        "count" : 2
                                },
                                {
                                        "score" : 4,
                                        "count" : 1
                                },
                                {
                                        "score" : 5,
                                        "count" : 1
                                },
                                {
                                        "score" : 3,
                                        "count" : 1
                                }
                        ]
                },
                {
                        "type" : "delivery",
                        "scores" : [
                                {
                                        "score" : 3,
                                        "count" : 1
                                },
                                {
                                        "score" : 4,
                                        "count" : 1
                                },
                                {
                                        "score" : 2,
                                        "count" : 1
                                },
                                {
                                        "score" : 5,
                                        "count" : 2
                                }
                        ]
                },
                {
                        "type" : "timeliness",
                        "scores" : [
                                {
                                        "score" : 1,
                                        "count" : 1
                                },
                                {
                                        "score" : 5,
                                        "count" : 1
                                },
                                {
                                        "score" : 4,
                                        "count" : 1
                                },
                                {
                                        "score" : 3,
                                        "count" : 1
                                },
                                {
                                        "score" : 2,
                                        "count" : 1
                                }
                        ]
                }
        ]
}
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Awesome! I'll try this one when i get home and will leave you a feedback. Yes i'm only trying to get it for single user
@John try at home then. If you are just matching single documents then you "should" use _id. You would gasp audibly if you were aware of the additional internal instructions required to simply look up an individual document by anything other than the primary key.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.