MongoDB Aggreggation: Group and get multiple counts based on value

Question

I have the following in an aggregation:

[  
  { _id: 610b678502500b0646923801, feeling: 'dislike' },
  { _id: 610b678502500b0646923629, feeling: 'like' },
  { _id: 610b67a602500b064693a667, feeling: 'love' },
  { _id: 610b678d02500b06469290fd, feeling: 'like' },
  { _id: 610b678502500b06469238f3, feeling: 'love' },
  { _id: 610b678502500b06469237ed, feeling: 'love' },
  { _id: 610b678502500b064692389e, feeling: 'like' },
  { _id: 610b678502500b0646923bd8, feeling: 'love' },
  { _id: 610b678502500b06469237e0, feeling: 'love' },
  { _id: 610b678502500b0646923674, feeling: 'love' },
  { _id: 610b680b02500b0646981b3a, feeling: 'dislike' },
  { _id: 610b678702500b0646925096, feeling: 'love' },
  { _id: 610b678502500b0646923810, feeling: 'like' },
  { _id: 610b678d02500b06469292fb, feeling: 'dislike' },
  { _id: 610b678502500b06469238b4, feeling: 'like' }
  ...
]

I want to group by _id, and show the number of items for each feeling. The expected results would be:

[ 
  {
    _id: 610b678502500b0646923801, 
    love: 2, 
    like: 4, 
    dislike: 6
  },
  {
    _id: 610b678502500b06469237ed, 
    love: 8, 
    like: 2, 
    dislike: 5
  }
  ...
]

This question and answer with $sum and $cond or switch might help: https://stackoverflow.com/q/14102596/642579 — Markus, Jun 17 '22 at 20:13
feelings can be only those 3? or we dont know what a feeling value can be? because if we have like 5-6 feelings, query is easy to be made — Takis, Jun 17 '22 at 22:06

R2D2 · Answer 1 · 2022-06-17T21:48:55.240

Perhaps something like this:

db.collection.aggregate([
 {
  $unwind: "$feeling"
 },
 {
  $group: {
  _id: {
    i: "$_id",
    f: "$feeling"
   },
  cnt: {
    $sum: 1
  }
  }
  },
 {
$project: {
  _id: "$_id.i",
  a: [
    {
      k: "$_id.f",
      v: "$cnt"
    }
  ]
 }
},
{
$group: {
  _id: "$_id",
  s: {
    "$mergeObjects": {
      "$arrayToObject": "$a"
     }
    }
   }
 },
 {
  $replaceRoot: {
    newRoot: {
      $mergeObjects: [
       "$s",
       "$$ROOT"
     ]
    }
  }
 },
 {
  $project: {
    s: 0
  }
 }
])

Explained:

At the unwind stage just unwinding to simulate duplicated _id

--- in your aggregation you need to continue from here ---

Group based on _id & feeling to have the count per feeling
Project the counts to suitable for arrayToObject key/values
Group with mergeObjects + arrayToObject to receive the count per feeling
Replace the root with merged _id + counts
Project to remove the temporary variable "s"

Playground

Thanks R2D2 - the _id is not unique here. This is in the middle of an aggregation where I have unwound across multiple users. So if the _id represents a book (which does have a unique _id), then this array is all loves, likes and dislikes across all users and all books. Now I want to group by book and the number of loves, likes and dislikes for each book. — gregg, Jun 17 '22 at 20:53

score 0 · Accepted Answer · answered Jun 17 '22 at 22:13

Query

group by id
because we know that we have only 3 possible groups, we create those groups and sum based on condition, if feeling match with the field name 1 else 0

Playmongo

aggregate(
[{"$group": 
   {"_id": "$id",
    "dislike": 
     {"$sum": {"$cond": [{"$eq": ["$feeling", "dislike"]}, 1, 0]}},
    "like": {"$sum": {"$cond": [{"$eq": ["$feeling", "like"]}, 1, 0]}},
    "love": {"$sum": {"$cond": [{"$eq": ["$feeling", "love"]}, 1, 0]}}}}])

MongoDB Aggreggation: Group and get multiple counts based on value

2 Answers2