CODEWITHSUNDEEP
mongodb
matan
matan

Reputation: 107

MongoDB count value of attribute and group by month and year

I have documents in my mongodb and i try to count every same description i have and classify to month they created.

I want to return an array that include array of objects that includes month number with sub array of description value with the count.

I want to be able to choose which description value to count and choose by what year he will show me the data for example this is my documents:

    [
  {
    "username": "ron",
    "skills": [
      {
        "rank": "high",
        list: [
          {
            "subject": "Football"
          },
          {
            "subject": "Swim"
          }
        ]
      },
      {
        "rank": "low",
        list: [
          {
            "subject": "Baseball"
          },
          
        ]
      }
    ],
    "duration": 0,
    "date": ISODate("2021-07-24T12:05:27.127Z"),
    "createdAt": ISODate("2021-07-24T12:05:49.985Z"),
    "updatedAt": ISODate("2021-07-24T12:05:49.985Z"),
    "__v": 0
  },
  {
    "username": "john",
    "skills": [
      {
        "rank": "low",
        list: [
          {
            "subject": "Football"
          },
          
        ]
      }
    ],
    "duration": 0,
    "date": ISODate("2021-07-25T12:05:53.000Z"),
    "createdAt": ISODate("2021-07-24T12:05:59.249Z"),
    "updatedAt": ISODate("2021-07-24T12:05:59.249Z"),
    "__v": 0
  },
  {
    "username": "david",
    "skills": [
      {
        "rank": "high",
        list: [
          {
            "subject": "Football"
          },
          {
            "subject": "Baseball"
          }
        ]
      },
      {
        "rank": "low",
        list: [
          {
            "subject": "Swim"
          },
          
        ]
      }
    ],
    "duration": 0,
    "date": ISODate("2021-08-26T12:06:13.000Z"),
    "createdAt": ISODate("2021-07-24T12:06:21.328Z"),
    "updatedAt": ISODate("2021-07-24T12:06:21.328Z"),
    "__v": 0
  },
  {
    "username": "david",
    "skills": [
      {
        "request": "high",
        list: [
          {
            "subject": "Swim"
          },
          
        ]
      },
      {
        "request": "low",
        list: [
          {
            "subject": "Football"
          },
          {
            "subject": "Baseball"
          },
          
        ]
      }
    ],
    "duration": 0,
    "date": ISODate("2021-01-21T13:07:50.000Z"),
    "createdAt": ISODate("2021-07-24T12:08:05.552Z"),
    "updatedAt": ISODate("2021-07-24T12:14:51.285Z"),
    "__v": 0
  },
  {
    "username": "david",
    "skills": [
      {
        "rank": "high",
        list: [
          {
            "subject": "Football"
          },
          
        ]
      },
      
    ],
    "duration": 0,
    "date": ISODate("2022-01-21T13:07:50.000Z"),
    "createdAt": ISODate("2022-07-24T12:08:05.552Z"),
    "updatedAt": ISODate("2022-07-24T12:14:51.285Z"),
    "__v": 0
  }
]

The result I expect to get if i want to get match only the description that equal to "Football" or "Baseball":

 {
    [ {"month_number":7,"result":[{"description":'Football',"count":2},{"description":"Baseball","count":1}]},
          {"month_number":8,"result":[{"description":'Football',"count":1}]}]
}

I'm new to mongodb ... so far I have been able to count how many there are of each value and display only the values I want but I do not know how to classify it into months depending on the year I choose.

I tried this:

db.exercises.aggregate([{$match:{ $and:[{description:{$in: ["Baseball","Football"]}}]}} ,{$group:{_id:"$description",count:{$sum:1}}}])

and this

db.exercises.aggregate(
  [
    {
      $project:
        {
          _id: 0,
          year: { $year: "$date" },
          month: { $month: "$date" },
          description:"$description"
        }
    },
    {
        $match:{$and:[{description:{$in: ["Baseball","Football","Swim"]},year:2021}]}
     },
     {$group:{_id:"$description" , count:{$sum:1}}}
     
  ]
)

Upvotes: 0

Views: 110

Answers (1)

Rajdeep D
Rajdeep D

Reputation: 3910

You need add 1 more grouping i.e. by month.

Working playground

db.collection.aggregate([
  {
    $project: {
      _id: 0,
      year: {
        $year: "$date"
      },
      month: {
        $month: "$date"
      },
      description: "$description"
    }
  },
  {
    $match: {
      $and: [
        {
          description: {
            $in: [
              "Baseball",
              "Football",
              "Swim"
            ]
          },
          year: 2021
        }
      ]
    }
  },
  {
    $group: {
      _id: {
        description: "$description",
        month: "$month"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: {
        month_number: "$_id.month"
      },
      results: {
        $push: {
          description: "$_id.description",
          count: "$count"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      month_number: "$_id.month_number",
      results: 1
    }
  }
])

Upvotes: 1

Related Questions