node.jsmongodbmongodb-nodejs-driver

Mongodb Distinct Query with Count


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.

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


Solution

  • 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