php - 我们如何在 MongoDB 聚合查询中对单个数组元素求和？

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:hello@lhd.net"    },    "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
                        )

还有，在MongoDB聚合查询中，如何同时使用$last和$sum？在我的结果中，有 2 个不同 id (192,67) 的 2 个原始分数(最后一个语句)。我想将所有多个 id 的分数加起来，如 4 + 6 = 10，但只需要最后一条语句的最后分数。我无法在线上使用 $last 和 $sum。请检查

Answer 1

看起来您只需要一个组。所以分组 id 应该为空。如果您关心最后一条记录应该是什么，您可能想要添加一个排序。未经测试。

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

这是 mongo shell 版本。

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"}
      }
    }
])

输出:

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

更新 更改为包括每个组最后一个语句的总和。第一个分组是按 Actor 姓名进行的，并返回每个组的最后一条语句。第二组对所有最后一条语句求和。

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"
        },
    }
}])