I want to count three fields in a nodejs mongodb column and need to group it by month

user_12470042 0
Viewed 48

Sample Data Set

I want to count no of green,red,amber in each month (ie: grouping by month)like in the below eg:

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

say _id represents the month (2:Feb)

note:- I am retrieving Data from mongodb and the Date column is in ISO date type format

Can anyone help me?

aggregation-framework mongodb-query node.js mongodb arrays
2 Answers

Demo - 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

user_22242650

Try this query:

db.colors.aggregate([
    {
        $group: {
            _id: {
                month: { $month: "$Date" },
                color: "$Rag"
            },
            count: { $sum: 1 }
        }
    },
    {
        $group: {
            _id: "$_id.month",
            array: {
                $push: {
                    k: "$_id.color",
                    v: "$count"
                }
            }
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $arrayToObject: {
                    $concatArrays: [[{ k: "_id", v: "$_id" }], "$array"]
                }
            }
        }
    }
]);

Output for the test data attached in the question:

{
    "_id" : 3,
    "Red" : 1,
    "Amber" : 14,
    "Green" : 7
}

Data in colors collection:

[
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Green"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Green"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Green"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Green"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Green"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Green"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Green"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Red"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  },
  {
    "_id": ObjectId("..."),
    "Date": ISODate("2021-03-16T00:00:00.000+05:30"),
    "Rag": "Amber"
  }
]
user_50704600
Related