MongoDb Aggregation conditional count group by in nested array

Question

I am having trouble with the below situation. I would like to count 'modAttmpt.retrySess.meta.k.gradedActionsFeedback' with value 'Very Good!' and group by 'modAttmpt.modId'.

So far tried below with no luck:

db.myCollection.aggregate()
.unwind("$modAttmpt")
.unwind("$modAttmpt.retrySess")
.project(
    {
        'modAttmpt.modId' :1,
        'modAttmpt.retrySess.dur': 1,
        'modAttmpt.retrySess.modPassSt': 1,
        'modAttmpt.retrySess.meta' : 1 ,
        'totalPass' : 
        { 
            $cond: [
                { $eq: ["$modAttmpt.retrySess.modPassSt", 1] },
                1, 0
            ]
        },
        'passWithGood' : 
        {
            $cond: { if: { $eq: ["$modAttmpt.retrySess.meta.k", 'gradedActionsFeedback'] }, then: 1, else: 0 }
        } 
    }
)
.group
(
    {
        _id: "$modAttmpt.modId",
        'avg': {$avg: '$modAttmpt.retrySess.dur'},
        'totalPass' :{ $sum: '$totalPass'},
        'passWithGood' : { $sum: '$passWithGood'} 
    }
)

Sample Data:

 {
        "_id" : ObjectId("617c10a8e1136c7cf51ffd9d"),
        "modAttmpt" : {
            "modId" : "mod_home",
            "retrySess" : {
                "meta" : [
                    {
                        "k" : "characterSelected",
                        "v" : "Male"
                    },
                    {
                        "k" : "gradedActionsFeedback",
                        "v" : "Very Good!"
                    }
                ],
                "dur" : 432,
                "modPassSt" : 1
            }
        },
        "totalPass" : 1,
        "passWithGood" : 0
    },
    {
        "_id" : ObjectId("617c10a8e1136c7cf51ffd94"),
        "modAttmpt" : {
            "modId" : "mod_home",
            "retrySess" : {
                "meta" : [
                    {
                        "k" : "characterSelected",
                        "v" : "Male"
                    },
                    {
                        "k" : "gradedActionsFeedback",
                        "v" : "Very Good!"
                    }
                ],
            }
        },
        "totalPass" : 1,
        "passWithGood" : 0
    },
    {
        "_id" : ObjectId("617c10a8e1136c7cf51ffd92"),
        "modAttmpt" : {
            "modId" : "mod_clq",
            "retrySess" : {
                "meta" : [
                    {
                        "k" : "gradedActionsFeedback",
                        "v" : "Very Good!"
                    }
                ],
            }
        },
        "totalPass" : 1,
        "passWithGood" : 0
    },
    {
        "_id" : ObjectId("617c10a8e1136c7cf51ffd91"),
        "modAttmpt" : {
            "modId" : "mod_home",
            "retrySess" : {
                "meta" : [
                    {
                        "k" : "gradedActionsFeedback",
                        "v" : "Good!"
                    }
                ],
            }
        },
        "totalPass" : 1,
        "passWithGood" : 0
    },
    {
        "_id" : ObjectId("617c10a8e1136c7cf51ffd95"),
        "modAttmpt" : {
            "modId" : "mod_clq",
            "retrySess" : {
                "meta" : [
                    {
                        "k" : "gradedActionsFeedback",
                        "v" : "Very Good!"
                    }
                ],
            }
        },
        "totalPass" : 1,
        "passWithGood" : 0
    }

hhharsha36 · Accepted Answer · 2021-12-25 09:01:36Z

You can make use of $reduce operator to calculate modAttmpt.retrySess.meta.k.gradedActionsFeedback with value Very Good!

Replace your $project stage with the one below:

db.collection.aggregate([
  {
    "$project": {
      "modAttmpt.modId": 1,
      "modAttmpt.retrySess.dur": 1,
      "modAttmpt.retrySess.modPassSt": 1,
      "modAttmpt.retrySess.meta": 1,
      "totalPass": {
        $cond: [
          {
            $eq: [
              "$modAttmpt.retrySess.modPassSt",
              1
            ]
          },
          1,
          0
        ]
      },
      "passWithGood": {
        $cond: {
          if: {
            $eq: [
              "$modAttmpt.retrySess.meta.k",
              "gradedActionsFeedback"
            ]
          },
          then: 1,
          else: 0
        }
      },
      "passWithGood2": {
        "$reduce": {
          "input": "$modAttmpt.retrySess.meta",
          "initialValue": 0,
          "in": {
            "$add": [
              {
                "$cond": {
                  "if": {
                    "$eq": [
                      "$$this.v",
                      "Very Good!"
                    ]
                  },
                  "then": 1,
                  "else": 0,
                  
                },
                
              },
              "$$value",
              
            ]
          }
        }
      }
    }
  },
  {
    "$group": {
      _id: "$modAttmpt.modId",
      "avg": {
        $avg: "$modAttmpt.retrySess.dur"
      },
      "totalPass": {
        $sum: "$totalPass"
      },
      "passWithGood": {
        $sum: "$passWithGood2"
      }
    },
    
  },
  
])

Mongo Playground Sample Execution

Collectives™ on Stack Overflow

MongoDb Aggregation conditional count group by in nested array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related