I have a schema like below in mongodb

let reactions = [

  {
      messageId: 1,
      reactions: [
          {
              "userid": "7212898122",
              "reactionId": 2,
              "date": "2022-05-12T18:55:49.943Z"
          },
          {
              "userid": "8171763225",
              "reactionId": 2,
              "date": "2022-05-12T19:27:34.324Z"
          },
          {
              "userid": "8782323232",
              "reactionId": 3,
              "date": "2022-05-12T18:55:49.943Z"
          }
      ]

  },

  {
      messageId: 2,
      reactions: [
          {
              "userid": "7212898122",
              "reactionId": 1,
              "date": "2022-05-12T18:55:49.943Z"
          },
          {
              "userid": "8171763225",
              "reactionId": 2,
              "date": "2022-05-12T19:27:34.324Z"
          },
          {
              "userid": "8782323232",
              "reactionId": 1,
              "date": "2022-05-12T18:55:49.943Z"
          }
      ]

  }

]

I want to result like this

    let result = [
    {
        messageId:1,
        count:{
            2:2,
            3:1
        }
    },

    {
        messageId:1,
        count:{
            1:2,
            2:1
        }
    }
]

Means two user reacted on reactionId 2 and One user reacted on reactionId 1, same like that for all messageId

I tried with group, but didn't exactly get result that I want. Please answer.

===================================

CodePudding user response：

You can do something like:

1. $unwind to separate reactions
2. $group both by messageId and reactionId, to get the count you want
3. $group by messageId to 'revert' the $unwind and use k and v in the array to enable transforming into object.
4. $project to format as object

db.collection.aggregate([
  {
    $unwind: "$reactions"
  },
  {
    $group: {
      _id: {
        messageId: "$messageId",
        reactionId: "$reactions.reactionId"
      },
      count: {$sum: 1}
    }
  },
  {
    $group: {
      _id: "$_id.messageId",
      count: {
        $push: {k: {$toString: "$_id.reactionId"}, v: "$count"}}
    }
  },
  {
    $project: {
      messageId: "$_id",
      _id: 0,
      count: {$arrayToObject: "$count" }
    }
  }
])

But on mongodb the key should be string.

playground example