Question 116476 in Mongodb Question posted in
The official documentation can be found here.

Mongodb sum of views by day name

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?

Tags:

3

Answers


  1. 0

    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
    }
]
    Login or Signup to reply.
  2. 0

    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'
   }
  }
 }
}]);
    Login or Signup to reply.
  3. 0

    You can do it like this:

    • $set and $isoDayOfWeek – to calculate the day of week based on created_at property

    • $group and $sum – to sum all views for each day of the week

    db.collection.aggregate([
  {
    "$set": {
      "dayOfWeek": {
        "$isoDayOfWeek": "$created_at"
      }
    }
  },
  {
    "$group": {
      "_id": "$dayOfWeek",
      "count": {
        "$sum": "$views"
      }
    }
  }
])

    Note: In the response, 1 is Sunday and 7 is Saturday.

    Working example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search