Despite looking at many other posts I can't figure out what I'm doing wrong. I want to do a simple "count and group by", so I found out I need to use collection.aggregate....

Here is my [pubkey].ts where I execute the aggregation, basically I want to count each entry grouped by address sp I'd get something like :

{address1: 6},
{address2: 1},
...

import { connect } from '../../../utils/db'
import Raffles from '../../../utils/db/raffle'

export default async function handler(req, res) {
    const { method } = req
    const { pubkey } = req.query
    await connect()
    switch (method) {
        case 'POST':
          ...
          break
        case 'GET':
            try{
                const ticket = await Raffles.aggregate([{
                    "$match": {
                        "address": pubkey
                    }
                },{
                    "$count": { $sum: 1}
                }])

                res.status(201).json({ success: true, data: ticket })
            } catch (error){
                res.status(400).json({ success: false, data: error })
            }
            break
        default:
          res.status(400).json({ success: false })
          break
      }
}

I can't figure out how to do the summing part, if I replace "$count": "xxyz" I get a success but right now with "$count" : {$sum: 1} I get an error

Any ideas ?

CodePudding user response：

If you use the $count stage, it only receives a string parameter for the name of the field, so your pipeline could be like this:

[
  {
    "$match": {
      "address": pubkey
    }
  },
  {
    "$count": "total"
  }
]

It filters the documents that match the address and then gets the count. If you want the count of documents grouped by address, you can use this $group stage in the pipeline (in this case $count is an aggregation accumulator, so it doesn't take any parameter):

[
  {
    $group: {
      "_id": "$address",
      "total": {
        $count: {}
      }
    }
  }
]

That should give you some results like this:

{_id: address1, total: 6}
{_id: address2, total: 1}