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
  }
  ...
]

CodePudding user response：

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]}}}}])

CodePudding user response：

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:

0. At the unwind stage just unwinding to simulate duplicated _id

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

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

Playground