I am new to MongoDB and I am not sure how to solve in an efficient way this problem. In my app, a user can choose between one object or another (multiple times, by giving positive/negative ratings). What I want to do is to count how many times an object has been rated positive and negative. To give you a more precise idea, here is some data sample (I removed some fields to make it simpler) (I need the data in this form because a user is not allowed to rate the same pair of objects twice):
{
"_id" : ObjectId("579688a5336f04d208747fe8"),
"positive" : ObjectId("5796708d58cc25ab040a6cda"),
"negative" : ObjectId("5796705c58cc25ab040a6cd7")
},
{
"_id" : ObjectId("579688a7336f04d208747fe9"),
"positive" : ObjectId("5796706358cc25ab040a6cd8"),
"negative" : ObjectId("5796708058cc25ab040a6cd9")
},
{
"_id" : ObjectId("579688a8336f04d208747fea"),
"positive" : ObjectId("5796708d58cc25ab040a6cda"),
"negative" : ObjectId("5796706358cc25ab040a6cd8")
},
{
"_id" : ObjectId("579688a8336f04d208747feb"),
"positive" : ObjectId("5796708d58cc25ab040a6cda"),
"negative" : ObjectId("5796706358cc25ab040a6cd8")
}...
I need to run a command which returns data in this way:
{
"_id": ObjectId("5796708d58cc25ab040a6cda"),
"positiveCount": 3,
"negativeCount": 0
},
{
"_id": ObjectId("5796705c58cc25ab040a6cd7"),
"positiveCount": 0,
"negativeCount": 1
}, ...
So far I am able to count just the number of positive or negative ratings separately using this command:
db.getCollection('votes').aggregate([
{$group:{_id:'$positive', count: {$sum:1}}}
]);
This works but only to count either positive or negative votes. This is the result I get
{
"_id" : ObjectId("57971bb60711a9a6046dccc7"),
"count" : 7.0
},
{
"_id" : ObjectId("5796708058cc25ab040a6cd9"),
"count" : 21.0
}...
My solution so far is to run the query twice (one for positive and one for negative ratings) and then merge them in the app level. I am asking you if there is a better and more performant way to achieve this result. Thanks in advice.