CODEWITHSUNDEEP
javascriptmongodbtypescriptmongooseaggregation-framework
Timon
Timon

Reputation: 447

Build the total sum of aggregated result

Is it somehow possible to add a field to the aggregated result? My goal is to have a total sum for all results. Currently, I just reduce the result, but I believe this is not as performant as solving it through a query.

aggregate([
      {
        $match: {
          time: { $gte: start, $lte: end },
        },
      },
      {
        $group:
          {
            _id: { $dateToString: { format: '%Y-%m-%d', date: '$time' } },
            totalAmount: { $sum: '$payment.amount' },
          },
      },
    ]).exec().then((result) => {
      return {
        total: result.reduce(((acc, curr) => acc + curr.totalAmount), 0),
        dates: result,
      };
    });

result is:

{
   "_id":"2020-06-06",
   "totalAmount":12
},
{
   "_id":"2020-07-06",
   "totalAmount":12
}

Any idea how I can get the total amount for all, looking like this but without that reduce part?

{
    "total": 24,
    "dates": [
        {
            "_id": "2020-06-06",
            "totalAmount": 12,      
        },
        {
            "_id": "2020-07-06",
            "totalAmount": 12,    
        }
    ]
}

Upvotes: 1

Views: 44

Answers (1)

Ashh
Ashh

Reputation: 46491

Either you can use two queries simultaneously

const [result, totalAmount] = await Promise.all([
  Model.aggregate([
    { $match: { time: { $gte: start, $lte: end } } },
    {
      $group: {
        _id: { $dateToString: { format: "%Y-%m-%d", date: "$time" } },
        totalAmount: { $sum: "$payment.amount" },
      }
    },
  ]),
  Model.aggregate([
    { $match: { time: { $gte: start, $lte: end } } },
    {
      $group: {
        _id: null,
        totalAmount: { $sum: "$payment.amount" },
      }
    },
  ])
])

return {
  total: result,
  dates: totalAmount,
}

Or can use $facet

const result = await Model.aggregate([
  { $match: { time: { $gte: start, $lte: end } } },
  {
    $facet: {
      result: [
        {
          $group: {
            _id: {
              $dateToString: { format: "%Y-%m-%d", date: "$time" },
            },
            totalAmount: { $sum: "$payment.amount" },
          },
        },
      ],
      totalAmount: [
        {
          $group: {
            _id: null,
            totalAmount: { $sum: "$payment.amount" },
          },
        },
      ],
    },
  },
]);


return {
  total: _.get(result, "[0].result", []),
  dates: _.get(result, "[0].totalAmount.totalAmount", 0),
}

Upvotes: 1

Related Questions