Assume I have the following data:

[{
  "type" : "DIVIDEND_OR_INTEREST",
  "netAmount" : 2.43,
  "transactionDate" : "2019-01-01T17:02:36 0000",
  "transactionId" : 1,
  "transactionItem" : {
    "instrument" : {
      "symbol" : "SPHD"
    }
  }
},
{
  "type" : "DIVIDEND_OR_INTEREST",
  "netAmount" : 5.00,
  "transactionDate" : "2019-01-01T17:02:36 0000",
  "transactionId" : 2,
  "transactionItem" : {
    "instrument" : {
      "symbol" : "ATT"
    }
  }
},
{
  "type" : "DIVIDEND_OR_INTEREST",
  "netAmount" : 2.43,
  "transactionDate" : "2019-02-01T17:02:36 0000",
  "transactionId" : 3,
  "transactionItem" : {
    "instrument" : {
      "symbol" : "SPHD"
    }
  }
},
{
  "type" : "DIVIDEND_OR_INTEREST",
  "netAmount" : 5.00,
  "transactionDate" : "2019-02-01T17:02:36 0000",
  "transactionId" : 4,
  "transactionItem" : {
    "instrument" : {
      "symbol" : "ATT"
    }
  }
}]

I want to group the data by year and get a total sum for that year. I also want an array of the items used during the group, grouped by a field and summed, if that makes sense. This is ultimately what I want to end up with:

{
    "year": [
        {
            "year": "2019",
            "totalYear": 14.86,
            "dividends": [
                {
                    "symbol": "T",
                    "amount": 10.00
                },
                {
                    "symbol": "SPHD",
                    "amount": 4.86
                }
            ]
        }
    ]
}

Below is the code I have written so far using Mongoose. The problem is that I can't figure out how to group and sum the items that I added to the set. I could always do that in the application layer but I was hoping to accomplish this entirely inside of a query.:

const [transactions] = await Transaction.aggregate([
      { $match: { type: TransactionType.DIVIDEND_OR_INTEREST, netAmount: { $gte: 0 } } },
      {
        $facet: {
          year: [
            {
              $group: {
                _id: { $dateToString: { format: '%Y', date: '$transactionDate' } },
                totalYear: { $sum: '$netAmount' },
                dividends: {
                  $addToSet: {
                    symbol: '$transactionItem.instrument.symbol',
                    amount: '$netAmount',
                  },
                },
              },
            },
            { $sort: { _id: 1 } },
            {
              $project: {
                year: '$_id',
                totalYear: { $round: ['$totalYear', 2] },
                dividends: '$dividends',
                _id: false,
              },
            },
          ],
        },
      },
    ]).exec();

CodePudding user response：

• It requires to do two group stages,
• First group by year and symbol
• Second group by only year
• If the transactionDate field has date type value then just use $year operator to get the year
• I would suggest you do $sort after the immediate $match stage to use an index if you have created or planning for future

const [transactions] = await Transaction.aggregate([
  {
    $match: {
      type: TransactionType.DIVIDEND_OR_INTEREST,
      netAmount: { $gte: 0 }
    }
  },
  { $sort: { transactionDate: 1 } },
  {
    $facet: {
      year: [
        {
          $group: {
            _id: {
              year: { $year: "$transactionDate" },
              symbol: "$transactionItem.instrument.symbol"
            },
            netAmount: { $sum: "$netAmount" }
          }
        },
        {
          $group: {
            _id: "$_id.year",
            totalYear: { $sum: "$netAmount" },
            dividends: {
              $push: {
                symbol: "$_id.symbol",
                amount: "$netAmount"
              }
            }
          }
        },
        {
          $project: {
            _id: 0,
            year: "$_id",
            totalYear: 1,
            dividends: 1
          }
        }
      ]
    }
  }
]).exec();

Playground