具有数组值总和的 MongoDB 聚合 [英] MongoDB Aggregation with sum of array values

问题描述

我有一个包含以下数据的集合:

I have a collection with the following data:

{
    "_id" : ObjectId("5516d416d0c2323619ddbca8"),
    "date" : "28/02/2015",
    "driver" : "user1",
    "passengers" : [
        {
            "user" : "user2",
            "times" : 2
        },
        {
            "user" : "user3",
            "times" : 3
        }
    ]
}
{
    "_id" : ObjectId("5516d517d0c2323619ddbca9"),
    "date" : "27/02/2015",
    "driver" : "user2",
    "passengers" : [
        {
            "user" : "user1",
            "times" : 2
        },
        {
            "user" : "user3",
            "times" : 2
        }
    ]
}

而且我想执行聚合，以便我知道某个乘客，有时是某个司机，在我的例子中，它将是:对于用户 1:[{ driver: user2, times: 2}]对于 user2:[{ driver: user1, times: 2}]对于 user3:[{ driver: user1, times: 3}, {driver: user2, times:2}]

And I would like to perform aggregation so that I will know for a certain passenger, times it was with a certain driver, in my example it would be: for user1: [{ driver: user2, times: 2}] for user2: [{ driver: user1, times: 2}] for user3: [{ driver: user1, times: 3}, {driver: user2, times:2}]

我对 mongo 很陌生，知道如何使用 sum 执行简单的聚合，但在它的内部数组以及我的主题本身在数组中时不知道.执行这种聚合的合适方法是什么，更具体地说，我如何在基于 express.js 的服务器中执行它?

Im quite new with mongo and know how to perform easy aggregation with sum, but not when its inside arrays, and when my subject is itself in the array. what is the appropriate way to perform this kind of aggregation, and in more specific, how I perform it in express.js based server?

推荐答案

为了使用聚合框架实现您的需求，第一个管道阶段将是 $match 对相关乘客的操作，将文档与乘客中的用户相匹配数组，后跟 $unwind 操作将前一个操作中的输入文档中的乘客数组解构，为每个元素输出一个文档.对解构数组的另一个 $match 操作如下，进一步过滤前一个文档流，只允许匹配的文档未经修改地传递到下一个管道阶段，该阶段正在投影所需的字段使用 $project 运算符.所以基本上你的 user3 聚合管道将是这样的:

To achieve your needs with aggregation framework, the first pipeline stage will be a $match operation on the passenger in question that matches the documents with the user in the passenger array, followed by the $unwind operation which deconstructs the passengers array from the input documents in the previous operation to output a document for each element. Another $match operation on the deconstructed array follows that further filters the previous document stream to allow only matching documents to pass unmodified into the next pipeline stage, which is projecting the required fields with the $project operator. So essentially your aggregation pipeline for user3 will be like:

db.collection.aggregate([
     {
        "$match": {
            "passengers.user": "user3"
        }
     },
     {
         "$unwind": "$passengers"
     },
     {
        "$match": {
            "passengers.user": "user3"
        }
     },
     {
         "$project": {
             "_id": 0,
            "driver": "$driver",
            "times": "$passengers.times"
        }
     }
])

结果:

/* 0 */
{
    "result" : [ 
        {
            "driver" : "user1",
            "times" : 3
        }, 
        {
            "driver" : "user2",
            "times" : 2
        }
    ],
    "ok" : 1
}

更新:

对于具有不同日期的驱动程序的重复分组，正如您提到的，您可以执行 $group 操作就在最后一个 $project 管道阶段之前使用 $sum 运算符计算总乘客次数:

For grouping duplicates on drivers with different dates, as you mentioned you can do a $group operation just before the last $project pipeline stage where you compute the total passengers times using the $sum operator:

db.collection.aggregate([
     {
        "$match": {
            "passengers.user": "user3"
        }
     },
     {
         "$unwind": "$passengers"
     },
     {
        "$match": {
            "passengers.user": "user3"
        }
     },
     {
         "$group": {
             "_id": "$driver", 
             "total": {
                 "$sum": "$passengers.times"
             }
         }
     },
     {
         "$project": {
            "_id": 0,
            "driver": "$_id",
            "total": 1
        }
     }
])

结果:

/* 0 */
{
    "result" : [ 
        {
            "total" : 2,
            "driver" : "user2"
        }, 
        {
            "total" : 3,
            "driver" : "user1"
        }
    ],
    "ok" : 1
}

这篇关于具有数组值总和的 MongoDB 聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！