How do we sum individual array elements in MongoDB aggregation query?

Question

Document :
  {
    "version": "1.0.0",
    "actor": {
        "objectType": "Agent",
        "name": "Test user",
        "account": {
            "homePage": "http://testing.com/",
            "name": "67"
        }
    },
    "verb": {
        "id": "http://adlnet.gov/expapi/verbs/completed",
        "display": {
            "en-US": "completed"
        }
    },
    "object": {
        "objectType": "Activity",
        "id": "http://localhost/action?id=cji",
        "definition": {
            "type": "http://adlnet.gov/expapi/activities/lesson",
            "name": {
                "en-US": "ps3"
            },
            "description": {
                "en-US": "ps3"
            }
        }
    },
    "timestamp": "2016-10-25T11:21:25.917Z",
    "context": {
        "extensions": {
            "http://localhost/eventinfo": {
                "sessionId": "1477393533327",
                "starttm": "1477394351210",
                "eventtm": "1477394485917",
                "course": "cji"
            }
        },
        "contextActivities": {
            "parent": [
                {
                    "objectType": "Activity",
                    "id": "http://localhost/id=cji"
                }
            ]
        }
    },
    "result": {
        "duration": "PT2M14.71S",
        "score": {
            "raw": 6,
            "max": 21
        }
    },
    "authority": {
        "objectType": "Agent",
        "name": "New Client",
        "mbox": "mailto:[email protected]"
    },
    "stored": "2016-10-25T11:20:29.666700+00:00",
    "id": "c7039783-371f-4f59-a665-65a9d09a2b7f"
}

We've got this PHP + MongoDB aggregation query:

    $condition = array(
                 array(
                '$match' => array(
                    'client_id' => $CFG->mongo_clientid,
                    'statement.actor.account.name' => array('$in'=> array('67','192','213')),
                    'statement.verb.id' => 'http://adlnet.gov/expapi/verbs/completed',
                    'statement.object.id' => 'http://localhost/action?id=cji'
                )),
                 array(
                '$group' => array(
                    '_id' =>  '$statement.actor.account.name' ,
                    //'totalpoints' =>array( '$sum' => array('$last' => '$statement.result.score.raw'))                
                    'laststatement' => array('$last' => '$statement.result.score.raw'),
                    //'sumtest' => array('$add' => ['$laststatement'])
                     )
                  )
            );
             $cursor = $collection->aggregate($condition);
             echo "";
             print_r($cursor);
             echo "";

which returns this result:

    Array
    (
        [result] => Array
            (
                [0] => Array
                    (
                        [_id] => 192
                        [laststatement] => MongoInt64 Object
                            (
                                [value] => 4
                            )

                    )

                [1] => Array
                    (
                        [_id] => 67
                        [laststatement] => MongoInt64 Object
                            (
                                [value] => 6
                            )

                    )

            )

        [ok] => 1
    )

How do we sum [laststatement].[value] of these individual array elements in MongoDB aggregation query?

[laststatement] => MongoInt64 Object
                        (
                            [value] => values goes here
                        )

Also, how do we use $last and $sum together in MongoDB aggregation query? In my result there are 2 raw scores(last statement) for 2 different id (192,67). I want to sum this scores like 4 + 6 = 10 for all multiple id's but want only the last scores from the last statement. I am unable to use $last and $sum on the line. Please check

Do you want to sum and result in a single group ? Please add data structure if you can. — s7vr
– s7vr, Commented Oct 25, 2016 at 15:21

s7vr · Accepted Answer · 2016-10-27 12:24:51Z

2

Looks like all you want is a single group. So the grouping id should be null. You may want to add a sort if you care for what last record should be. Not tested.

array(
      '$group' => array(
      '_id' =>  null ,
      'totalpoints' => array( '$sum' => '$statement.result.score.raw')                
      'laststatement' => array('$last' => '$statement.result.score.raw')
     )
)

Here is the mongo shell version.

aggregate([
    {
       $match :{
             "actor.account.name":{$in:["67","192","213"]},
             "verb.id":{$eq:"http://adlnet.gov/expapi/verbs/completed"},
             "object.id":{$eq:"http://localhost/action?id=cji"}
       }
    },
    {
      $group: {
          "_id": null,
          "totalpoints" : {$sum:"$result.score.raw"},                
          "laststatement" :{$last:"$result.score.raw"}
      }
    }
])

Output:

{ "_id" : null, "totalpoints" : 10, "laststatement" : 4 }

Update Changed to include the sum for the last statement from each group. The first grouping is by actor name and returns the last statement from each group. The second grouping sums all the last statement.

aggregate([{
    $match: {
        "actor.account.name": {
            $in: ["67", "192", "213"]
        },
        "verb.id": {
            $eq: "http://adlnet.gov/expapi/verbs/completed"
        },
        "object.id": {
            $eq: "http://localhost/action?id=cji"
        }
    }
}, {
    $group: {
        "_id": "$actor.account.name",
        "laststatement": {
            $last: "$result.score.raw"
        }
    }
}, {
    $group: {
        "_id": null,
        "totalpoints": {
            $sum: "$laststatement"
        },
    }
}])

edited Oct 27, 2016 at 12:24

answered Oct 26, 2016 at 8:09

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

3 Comments

Tanmay Sakhale Over a year ago

this is producing an output for only user 67 and giving total of all statements generated by 67 and it is coming as 60. we want the total of last statements of 67,192 and total is not coming as 10

s7vr Over a year ago

Updated the answer based on previous comment. Please verify.

Tanmay Sakhale Over a year ago

Thank you @Veeram it solved my problem, Now i am stuck in another query. Updated the question with the new issues.

Collectives™ on Stack Overflow

How do we sum individual array elements in MongoDB aggregation query?

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