mongoose 在MongoDB中使用$addToSet后进行分组和求和

nfg76nw0 于 2022-11-13 发布在 Go

关注(0)|答案(1)|浏览(86)

假设我有以下数据：

[{
  "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"
    }
  }
}]

我希望按年份对数据进行分组，并得到该年份的总和。如果有意义的话，我还希望得到分组过程中使用的项的数组，按字段分组并求和。这就是我最终希望得到的结果：

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

下面是我用Mongoose编写的代码。问题是我不知道如何对添加到集合中的项进行分组和求和。我可以在应用层中完成这些操作，但我希望完全在查询中完成。

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();

mongoose

来源：https://stackoverflow.com/questions/74414635/grouping-and-summing-after-using-addtoset-in-mongodb

1条答案

按热度按时间

mwg9r5ms1#

它需要做两个小组赛阶段，
第一组按year和symbol
第二组仅按year
如果transactionDate字段具有日期类型值，则只需使用$year运算符获取年份
如果您已经创建或计划将来使用索引，我建议您在立即$match阶段之后执行$sort

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

赞(0）回复(0）举报 2022-11-13

我来回答

mongoose 在MongoDB中使用$addToSet后进行分组和求和

1条答案

相关问题

热门标签

最新问答