skip to Main Content

so here’s my collection. This consists of users and their data

{
  userId: 6udg,
    data: [
       {
        date: 22-09-2022
        hits: 98
       },
      {
       date: 23-09-2022
       hits: 88
      },
      {
       date: 24-09-2022
       hits: 100
      },
      {
       date: 24-11-2022
       hits: 145
      },
      {
       date: 25-11-2022
       hits: 75
      }            
     ]
 },
{
  userId:  7tu5,
    data: [
       {
        date: 22-09-2022
        hits: 98
       },
      {
       date: 23-09-2022
       hits: 88
      },
      {
       date: 24-09-2022
       hits: 100
      },
      {
       date: 24-11-2022
       hits: 18
      },
      {
       date: 25-11-2022
       hits: 65
      }           
     ]
}

Here’s how I’m creating an aggregate with objects for hits filtered by week, month and year. First I match the user whose data we want to fetch. Then I use projection to get the custom fields that I want.

Users.aggregate([
                    {
                        $match: {
                            userId: req.params.userId
                        }
                    },
                    {
                        $project: {
                            _id: 0,
                            last_seven_days: {
                                $filter: {
                                    input: "$data",
                                    as: "index", 
                                    cond: {
                                        $and: [
                                            {
                                                $gte: [
                                                    "$$index.date",
                                                    new Date(moment().utc().startOf("week"))
                                                ]
                                            },
                                            {
                                                $lte: [
                                                    "$$index.date",
                                                    new Date(moment().utc().endOf("week"))
                                                ]
                                            }
                                        ]
                                    }
                                },
                            },
                            last_month: {
                                $filter: {
                                    input: "$data",
                                    as: "index", 
                                    cond: {
                                        $and: [
                                            {
                                                $gte: [
                                                    "$$index.date",
                                                    new Date(moment().utc().startOf("month"))
                                                ]
                                            },
                                            {
                                                $lte: [
                                                    "$$index.date",
                                                    new Date(moment().utc().endOf("month"))
                                                ]
                                            }
                                        ]
                                    }
                                }
                            },
                            last_year: {
                                $filter: {
                                    input: "$data",
                                    as: "index",
                                    cond: {
                                        $and: [
                                            {
                                                $gte: [
                                                    "$$index.date",
                                                    new Date(moment().utc().startOf("year"))
                                                ]
                                            },
                                            {
                                                $lte: [
                                                    "$$index.date",
                                                    new Date(moment().utc().endOf("month"))
                                                ]
                                            }
                                        ]
                                    }
                                }
                            }
                        }
                    }
                ])
 

what I want to do is add a key called ‘average’ in each last_seven_days , last_month, and last_year – containing the average hits for week, month and year respectively

Expected output:

{
   userId: 6udg
   last_seven_day:[ 
           avg: <avg>
           data:[
                 {
                   date:  24-11-2022,
                   hits: 145,
                 },
                 {
                   date:  25-11-2022,
                   hits: 75,
                 }
               ]
             ]
     }
                   
                

2

Answers


  1. You can simply add another projection step:

     db.collection.aggregate([
      {
        $project: {
          last_seven_day: {
            data: "$last_seven_day_arr",
            avg: {
              $avg: "$last_seven_day_arr.hits"
            }
          },
          last_month: {
            data: "$last_month_arr",
            avg: {
              $avg: "$last_month_arr.hits"
            }
          },
          last_year: {
            data: "$last_year_arr",
            avg: {
              $avg: "$last_year_arr.hits"
            }
          }
        }
      }
    ])
    

    See how it works on the playground

    Login or Signup to reply.
  2. You can use $filter to filter the records first. Then use another $avg to compute the average in filtered result.

    db.collection.aggregate([
      {
        "$match": {
          userId: "6udg"
        }
      },
      {
        "$addFields": {
          "last_seven_days": {
            data: {
              "$filter": {
                "input": "$data",
                "as": "d",
                "cond": {
                  $lt: [
                    {
                      "$dateDiff": {
                        "startDate": "$$d.date",
                        "endDate": "$$NOW",
                        "unit": "day"
                      }
                    },
                    7
                  ]
                }
              }
            }
          },
          "last_month": {
            data: {
              "$filter": {
                "input": "$data",
                "as": "d",
                "cond": {
                  $lt: [
                    {
                      "$dateDiff": {
                        "startDate": "$$d.date",
                        "endDate": "$$NOW",
                        "unit": "month"
                      }
                    },
                    1
                  ]
                }
              }
            }
          },
          "last_year": {
            data: {
              "$filter": {
                "input": "$data",
                "as": "d",
                "cond": {
                  $lt: [
                    {
                      "$dateDiff": {
                        "startDate": "$$d.date",
                        "endDate": "$$NOW",
                        "unit": "year"
                      }
                    },
                    1
                  ]
                }
              }
            }
          }
        }
      },
      {
        "$addFields": {
          "last_seven_days": {
            avg: {
              $avg: "$last_seven_days.data.hits"
            }
          },
          "last_month": {
            avg: {
              $avg: "$last_month.data.hits"
            }
          },
          "last_year": {
            avg: {
              $avg: "$last_year.data.hits"
            }
          }
        }
      }
    ])
    

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search