how to use aggregate for group and count in mongoose?

Question

I have json object in my db like as follows:

{
'name':'test1',
'game':'cric'
},

{
'name':'test1',
'game':'cric'
},

{
'name':'test1',
'game':'football'
},

{
'name':'test2'
'game':'football'
}

I am trying to get output as follows

 {
    'name':'test1'
    'game':[{cric: 2}, {football:1}],
    'totalCount': 3
    }

I used aggregate query for that.

group {'_id':{name:'$name'}, {game:{$addToSet:$game}}
project {name : $name, game: $game}

I got output as

{name: 'test1', 'game':[cric, football]}

Now i have question that, how can i get count of game. it current example for cricket it is 2 and for football 1 for test1 user

score 1 · Accepted Answer · edited May 23 '17 at 12:33

A similar question is answered here.

For your particular case it would be:

db.collection.aggregate([
  {
    $group: {
      _id:   { name: "$name", game: "$game" },
               games: { "$push": "$game" },
      total: { "$sum": 1 }
    }
  },
  {
    $group: {
      _id: { name: "$_id.name" },
      games: { $addToSet: { game: "$_id.game", sum:"$total" } } 
    }
  }
 ])

And the result should look like:

{
    "result" : [ 
        {
            "_id" : {
                "name" : "test1"
            },
            "games" : [ 
                {
                    "game" : "cric",
                    "sum" : 2
                }, 
                {
                    "game" : "football",
                    "sum" : 1
                }
            ]
        }, 
        {
            "_id" : {
                "name" : "test2"
            },
            "games" : [ 
                {
                    "game" : "football",
                    "sum" : 1
                }
            ]
        }
    ],
    "ok" : 1
}

how can we get total count? – Prashant Tapase Oct 22 '16 at 12:26 — Prashant Tapase, Oct 22 '16 at 12:26

score 1 · Answer 2 · answered Oct 22 '16 at 16:26

Here is my solution to test1 name record

    db.getCollection('COLLECTION_NAME').aggregate([
      {$match : {name : "test1"}},
      {
        $group: {
         _id:  "$game" ,
         total: { "$sum": 1 },
         name :  {$first : "$name"}
        }
      },
      {
       $group: {
        _id : "$name",
        games: { $addToSet: { game: "$_id", sum:"$total" } },
        totalCount : {$sum : "$total"}
      }
     }
   ])

how to use aggregate for group and count in mongoose?

2 Answers2