How to get and then group by date range -weekly- in MongoDB?-CodePudding

I have a MongoDB database populated with data and I am trying to query and get the results within a certain timeframe, in this case weeks. I am querying like this, which may not be right(?), to get all objects created within the last 6 weeks:

let weeklyPie = await Job.aggregate([
{ $match: { createdBy: mongoose.Types.ObjectId(req.user.userId) } },
{
  $match: {
    dateCreated: {
      $gte: moment().week(-6).toDate(),
      $lt: moment().startOf('week').toDate(),
    },
  },
},

]);

This is supposedly returning all my jobs created within the last six weeks.

Moreover I want to group them by week so all jobs created on week say 14 (current week) are returned in a group called weekOne, jobs created on week 13 are returned in a group called weekTwo and so on for the previous six weeks starting on the current one.

This is how my schema looks like in the DB:

{
        "_id": "6242dc818cd686716633be36",
        "company": "Ledner Inc",
        "position": "VP Product Management",
        "status": "interview",
        "jobType": "part-time",
        "jobLocation": "San José de Feliciano",
        "dateCreated": "2022-03-27T21:24:36.000Z",
        "createdBy": "62399147d817b82f5fe8f927",
        "createdAt": "2022-02-16T04:06:46.000Z",
        "updatedAt": "2022-04-07T10:51:05.171Z",
        "__v": 0,
        "interviewTime": "2022-04-20T16:30:00.000Z"
    }

So questions are:

What's the best way to get all my objects from the last six weeks? Is the snippet above correct? I am not too confident
How can I group them by weeks and get the whole object? Say 4 jobs were created on week 14 I want to get all 4 objects just for that week, and same with week 13 and so on.

Any hints?

CodePudding user response：

It seems you can use $week and $group. There is a simple solution without the group names:

db.collection.aggregate([
  {
    $match: {
      createdBy: mongoose.Types.ObjectId(req.user.userId),
      dateCreated: {
        $gte: moment().startOf('week').week(-6).toDate(),
        $lt: moment().startOf('week').toDate()
      },
    }
  },
  {
    "$addFields": {
      "week": {$week: "$dateCreated"}
    }
  },
  {
    $project: {data: "$$ROOT"}
  },
  {
    $group: {
      _id: "$data.week",
      week: {$first:"$data.week"},
      data: {$push: "$data"}
    }
  }
])

In order to get something similar to your expected results. You can see how it works on the playground.

If it is important to name the groups 'weekOne', 'weekTwo', you can do it by adding a step, finding the week group name from a given list of group names:

db.collection.aggregate([
  {
    $match: {
      createdBy: mongoose.Types.ObjectId(req.user.userId),
      dateCreated: {
        $gte: moment().startOf('week').startOf('week').toDate(),
        $lt: moment().startOf('week').toDate()
      },
    }
  },
  {
    "$addFields": {
      "weekNum": {$week: "$dateCreated"},
      "currentWeek": {$week: ISODate("2022-04-07T21:24:36.000Z")},
    }
  },
  {
    $project: {
      data: "$$ROOT",
      weeksOrder: [
        "WeekOne",
        "WeekTwo",
        "WeekTree",
        "WeekFour",
        "WeekFive",
        "WeekSix"
      ],
      weekIndex: {$subtract: ["$currentWeek", "$weekNum"]
      }
    }
  },
  {
    $project: {
      data: 1,
      weekGroup: {"$arrayElemAt": ["$weeksOrder", "$weekIndex"]}
    }
  },
  {
    $group: {
      _id: "$weekGroup",
      week: {$first: "$weekGroup"},
      data: {$push: "$data"}
    }
  }
])

As you can see on the playground as well