node.js - 我想计算 nodejs mongodb 列中的三个字段，需要按月分组

Question

我要数 green,red,amber在每个月(即:按月分组)如下所示，例如:

{_id:2,Green:20,Red:12,Amber:23}

说_id代表月份(2:Feb)
注意:- 我正在从 mongodb 检索数据，日期列采用 ISO 日期类型格式
谁能帮我？

Answer 1

演示 - https://mongoplayground.net/p/0N7b2uNZJNL

db.collection.aggregate({
  "$project": {
    color: 1,
    month: {
      "$substr": [ "$date", 0, { "$indexOfCP": [ "$date", "/" ] } // extract month from date string
      ]
    }
  }
},
{
  "$group": {
    "_id": {  month: "$month", color: "$color" },
    "count": { "$sum": 1 }
  }
},
{
  "$project": {
    month: "$_id.month",
    details: [ { color: "$_id.color", count: "$count" } ] // add color count to the array
  }
},
{
  $project: {
    _id: 0,
    month: 1,
    color: {
      $arrayToObject: { // get object from array like green: 1
        $map: {
          input: "$details", as: "pair", in: [ "$$pair.color",  "$$pair.count" ]
        }
      }
    }
  }
},
{
  "$group": {
    "_id": "$month",
    "color": { "$mergeObjects": "$color" } // merge all the colors
  }
},
{
  $replaceWith: {
    $mergeObjects: [ {  _id: "$_id"  }, "$color" ] // bring color out of the object
  }
})

$substr
$indexOfCP
$arrayToObject
$mergeObjects
$replaceWith