Applying group by in pipeline of aggregate with 2 matching documents-CodePudding

I need to apply group by on a field named material and sum aggregator on raisedGrnQuantity. Both this material and raisedGrnQuantity are in an array poDetails. I have constructed a aggregation pipeline to give a rough idea what actually I am trying to do :

var projectQry = [
                {
                    "$unwind": {
                        path : '$poDetails',
                        preserveNullAndEmptyArrays: true
                    }
                },
                {
                    $match: {
                        "trackNo": campaignId,
                        "vendorNo": vendorNo,
                        "poDetails.material": output[i].materialCode,
                        "status": "Pending"
                    }
                },
                {
                    $project: {
                        "poDetails.raisedGrnQuantity": 1,
                        "poDetails.material": 1
                    }
                }
            ];

The output of this aggregation pipeline is :

[
  {
    _id: 62de2e7f1ad8470012b9dc75,
    poDetails: { material: '300000114', raisedGrnQuantity: '20' }
  },
  {
    _id: 62de30ea1ad8470012b9dc78,
    poDetails: { material: '300000114', raisedGrnQuantity: '15' }
  }
]
[
  {
    _id: 62de2e7f1ad8470012b9dc75,
    poDetails: { material: '910000029', raisedGrnQuantity: '10' }
  },
  {
    _id: 62de30ea1ad8470012b9dc78,
    poDetails: { material: '910000029', raisedGrnQuantity: '5' }
  }
]

If we notice, there are two different arrays in output because I have two different matching documents in DB.

Now, what exactly I want to do is to apply group by on the material and sum aggregation on raisedGrnQuantity so that I will have output like below :

  {
    raisedGrnQuantity: '35'
    material: '300000114'
  },
  {
    raisedGrnQuantity: '15'
    material: '910000029'
  }

I tried to use below group by in the pipeline but it is not giving the correct output. Instead of summation of raisedGrnQuantity, it is showing 0.

$group: {
            _id: "$poDetails.material",
            raisedGrnQuantity: { $sum: "$poDetails.raisedGrnQuantity" }
        }

I suspect the issue is because of two matching documents which causes the output to be two different arrays.

CodePudding user response：

The issue you're having is because the type of "podDetail.raisedGrnQuantity" is string, so $sum is ignoring it.

All you need to do is cast it to be a number by using $toInt, like so:

  {
    $group: {
      _id: "$poDetails.material",
      raisedGrnQuantity: {
        $sum: {
          "$toInt": "$poDetails.raisedGrnQuantity"
        }
      }
    }
  }

Mongo Playground