I have this simple collection of views:

Views:

[
    {
        title: "cartoons",
        views: 1,
        created_at: 2022-10-03 12:00:00.000Z
    },
    {
        title: "songs",
        views: 4,
        created_at: 2022-10-04 12:00:00.000Z
    },
    {
        title: "lectures",
        views: 3,
        created_at: 2022-10-10 12:00:00.000Z
    },
    {
        title: "news",
        views: 2,
        created_at: 2022-10-05 12:00:00.000Z
    },
    {
        title: "movies",
        views: 6,
        created_at: 2022-10-07 12:00:00.000Z
    },
    {
        title: "tv series",
        views: 6,
        created_at: 2022-10-12 12:00:00.000Z
    }
]

Here I need to see how many views I got on each day of week in e.g 2 years

Expected Result:

{
    "monday": 4,
    "tuesday": 4,
    "wednesday": 8,
    "thursday": 0,
    "friday": 6,
    "saturday": 0,
    "sunday": 0,
}

Since I am very new to mongodb, Is this possible to perform such operation using query? If yes then can I get some help regarding this?

CodePudding user response：

What about this?

// select some random mongo database for testing
use("stack")

// at first clean collection
db.data.drop()


// populate with initial data
db.data.insertMany([
    {
        title: "cartoons",
        views: 1,
        created_at: ISODate("2022-10-03 12:00:00.000Z"),
    },
    {
        title: "songs",
        views: 4,
        created_at: ISODate("2022-10-04 12:00:00.000Z"),
    },
    {
        title: "lectures",
        views: 3,
        created_at: ISODate("2022-10-10 12:00:00.000Z"),
    },
    {
        title: "news",
        views: 2,
        created_at: ISODate("2022-10-05 12:00:00.000Z"),
    },
    {
        title: "movies",
        views: 6,
        created_at: ISODate("2022-10-07 12:00:00.000Z"),
    },
    {
        title: "tv series",
        views: 6,
        created_at: ISODate("2022-10-12 12:00:00.000Z"),
    }
])

// get results
p = [
    // get day of week for each record based on created_at date
    {
        $project: {
            weekDay: {
                $arrayElemAt: [
                    // mongo returns day numbers from 1 to 7, Sunday being 1
                    ["sunday", "monday", "tuesday", "wednesday", "thursday", "friday", "saturday"],
                    { $add: [ {$dayOfWeek: "$created_at"}, -1 ] }
                ]
            },
            views: 1,
            _id: 0,
        }
    },
    // count sum of views numbers for each weekday
    {
        $group: { _id: "$weekDay", total_views: {$sum: "$views"}  }
    },
    // reshape current results to make them easily convertable to one final object
    {
        $replaceRoot: {
            newRoot: { k: "$_id", v: "$total_views" }
        }
    },
    // step required to get just 1 document at the end
    {
        $group: {
            _id: 0,
            merged: { $push: "$$ROOT" }
        }
    },
    // fill in missing week days with 0 values and follow sorting order that we want
    {
        $project: {
            merged: {
                $mergeObjects: [
                    {
                        "monday": 0,
                        "tuesday": 0,
                        "wednesday": 0,
                        "thursday": 0,
                        "friday": 0,
                        "saturday": 0,
                        "sunday": 0,
                    },
                    {$arrayToObject: "$merged"},
                ]
            }
        }
    },
    // return field value that we want directly
    {
        $replaceRoot: { newRoot: "$merged"}
    }
]

// Run
db.data.aggregate(p)

And the result is

[
    {
    "monday": 4,
    "tuesday": 4,
    "wednesday": 8,
    "thursday": 0,
    "friday": 6,
    "saturday": 0,
    "sunday": 0
    }
]

CodePudding user response：

https://mongoplayground.net/p/QutCGjKiy6z

await db.collectionName.aggregate([{
 $addFields: {
  days: {
   $dayOfWeek: {
    $toDate: '$created_at'
   }
  }
 }
}, {
 $group: {
  _id: {
   days: '$days'
  },
  totalReview: {
   $sum: '$views'
  },
  daysCount: {
   $sum: 1
  }
 }
}, {
 $project: {
  _id: 0,
  totalReview: 1,
  day: {
   $switch: {
    branches: [
     {
      'case': {
       $eq: [
        '$_id.days',
        1
       ]
      },
      then: 'sunday'
     },
     {
      'case': {
       $eq: [
        '$_id.days',
        2
       ]
      },
      then: 'monday'
     },
     {
      'case': {
       $eq: [
        '$_id.days',
        3
       ]
      },
      then: 'tuesday'
     },
     {
      'case': {
       $eq: [
        '$_id.days',
        4
       ]
      },
      then: 'wednesday'
     },
     {
      'case': {
       $eq: [
        '$_id.days',
        5
       ]
      },
      then: 'thursday'
     },
     {
      'case': {
       $eq: [
        '$_id.days',
        6
       ]
      },
      then: 'friday'
     },
     {
      'case': {
       $eq: [
        '$_id.days',
        7
       ]
      },
      then: 'saturday'
     }
    ],
    'default': 'day unknown'
   }
  }
 }
}]);