CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Jorge De La Vega Carrasco
Jorge De La Vega Carrasco

Reputation: 65

Pass data from Sunday to week before in MongoDB

My aggregation gets the data of documents per week. In this case I'm getting data from days 18 to 24 of may:

{ "_id" : 20, "count" : 795 }
{ "_id" : 21, "count" : 221 }

Since 'week' in mongo starts from sundays, the data from sundays is creating a new week (In this case is 21). Is there any way I can transfer the data from Sundays to the week before or backwards?

The result would be:

{ "_id" : 20, "count" : 1016 }

Aggregation:

[{
    $match: {
       start_date: {
          $gte: ISODate('2020-05-18T00:00:01'),
          $lte: ISODate('2020-05-24T23:59:59')
       }
    }
}, {
    $project: {
       week: {
          $week: '$start_date'
       },
       solved: '$solved',
       survey: '$survey'
    }
}, {
    $group: {
       _id: '$week',
       count: {
          $sum: 1
       }
    }
}, {
    $sort: {
       _id: 1
    }
}]

UPDATE: Mongo

Upvotes: 0

Views: 236

Answers (1)

hhharsha36
hhharsha36

Reputation: 3349

I think the below query will do the trick.

The timezone key in if condition can be removed if your week-wise-sort is independent of the time zone of ISODate value in DB

db.<Collection-Name>.aggregate([
    {
        $match: {
           start_date: {
              $gte: ISODate('2020-05-18T00:00:01'),
              $lte: ISODate('2020-05-24T23:59:59')
           }
        }
    }, {
        $project: {
           week: {
               "$cond": {
                   "if": {"$eq": [{"$dayOfWeek": {"date": "$start_date", "timezone": "-0500"}}, 1]},
                   "then": {"$subtract": [{"$week": '$start_date'}, 1]},
                   "else": {"$week": '$start_date'}
               }
           },
           solved: '$solved',
           survey: '$survey'
        }
    }, {
        $group: {
           _id: '$week',
           count: {
              $sum: 1
           }
        }
    }, {
        $sort: {
           _id: 1
        }
    }
])

Upvotes: 1

Related Questions