Conditional Sum mongodb aggregation

Question

I am trying to sum a document array with condition.

{
        $project: {
            watched: 1,
            numberOfWatched: {
                $sum: {
                    // $cond: {
                    //     if: {
                    //         $eq: ['$watched.status', true],
                    //         then: 1,
                    //         else: 0
                    //     }
                    // }
                       $cond: [
                        { $eq: ['$watched.status', true]},
                        1,
                        0
                    ]
                }
            },
            durationWatched: {$sum: "$watched.duration"}
        }
    }

This is the output i am getting. Here you can see my watched array has a value with status true in it. So numberOfWatched was suppose to be 1. Instead its returning 0.

[
   {
      "_id":"5bacb579921406542350d254",
      "watched":[
         {
            "_id":"5baf44ee2369280542a4c41e",
            "duration":24.083333333333332,
            "user_id":"5bacbb05d2b8e4577b038f43",
            "video_id":"5bacb5b1921406542350d25e",
            "module_id":"5bacb588921406542350d256",
            "course_id":"5bacb579921406542350d254",
            "createdAt":"2018-09-29T09:25:02.107Z",
            "updatedAt":"2018-09-29T09:25:02.107Z",
            "__v":0,
            "status":true
         }
      ],
      "numberOfWatched":0,
      "durationWatched":24.083333333333332
   }
]

Ashh · Accepted Answer · 2018-09-29 10:58:38Z

2

You can try below $size and $filter aggregation

db.collection.aggregate([
  { "$project": {
    "watched": 1,
    "numberOfWatched": {
      "$size": {
        "$filter": {
          "input": "$watched",
          "cond": { "$eq": [ "$$this.status", true ] }
        }
      }
    },
    "durationWatched": {
      "$let": {
        "vars": {
          "dw": {
            "$filter": {
              "input": "$watched",
              "cond": { "$eq": [ "$$this.status", true ] }
            }
          }
        },
        "in": { "$sum": "$$dw.duration" }
      }
    }
  }}
])

edited Sep 29, 2018 at 10:58

answered Sep 29, 2018 at 10:29

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ahsan Aasim Over a year ago

i need to do this using $sum as i have to do the same thing with durationWatched using duration field under watched object

Ahsan Aasim Over a year ago

also seems $filter filters out the whole data that doesnt match the filter condition.

Ahsan Aasim Over a year ago

oh my bad.. Yes, its ok for numberOfWatched.

Collectives™ on Stack Overflow

Conditional Sum mongodb aggregation

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related