MongoDB集合聚合多个对象的总和

Question

给定以下集合:

    {
    "_id" : ObjectId("57bb00b1471bcc08e819bff3"),
    "BCNED3351" : {
        "timestamp" : 1471873201170.0,
        "totalOID" : {
            "backlog" : 1405,
            "inflow" : 396,
            "handled" : 341
        },
        "queues" : {
            "12" : {
                "backlog" : 5,
                "inflow" : 0,
                "handled" : 0
            },
            "30" : {
                "backlog" : 124,
                "inflow" : 1,
                "handled" : 1
            },
            "31" : {
                "backlog" : 15,
                "inflow" : 40,
                "handled" : 29
            },
            "33" : {
                "backlog" : 1,
                "inflow" : 12,
                "handled" : 12
            },
            "36" : {
                "backlog" : 285,
                "inflow" : 38,
                "handled" : 0
            },
            "40" : {
                "backlog" : 1,
                "inflow" : 1,
                "handled" : 0
            },
            "42" : {
                "backlog" : 968,
                "inflow" : 268,
                "handled" : 267
            },
            "44" : {
                "backlog" : 5,
                "inflow" : 35,
                "handled" : 32
            },
            "68" : {
                "backlog" : 1,
                "inflow" : 1,
                "handled" : 0
            }
        }
    }
}
/* 2 */
{
    "_id" : ObjectId("57bb00b2471bcc08e819bff4"),
    "PARED3100" : {
        "timestamp" : 1471873202167.0,
        "totalOID" : {
            "backlog" : 28,
            "inflow" : 0,
            "handled" : 0
        },
        "queues" : {
            "30" : {
                "backlog" : 25,
                "inflow" : 0,
                "handled" : 0
            },
            "31" : {
                "backlog" : 2,
                "inflow" : 0,
                "handled" : 0
            },
            "36" : {
                "backlog" : 1,
                "inflow" : 0,
                "handled" : 0
            }
        }
    }
}

我试图计算集合中每个对象的每个队列的所有积压，流入和处理元素的总和。到目前为止，这是我为一个没有成功的特定队列设计的:

var collection = db.collection('2016-08-23');
    collection.aggregate([
        {
            $group:{
                queue:'30', 
                backlog:
                {
                    $sum:{$add:['$BCNED3351.queues.30.backlog','$PARED3100.queues.30.backlog']}
                },
                inflow:
                {
                    $sum:{$add:['$BCNED3351.queues.30.inflow','$PARED3100.queues.30.inflow']}
                },
                handled:
                {
                    $sum:{$add:['$BCNED3351.queues.30.handled','$PARED3100.queues.30.handled']}
                }
            }
        }
    ], function(err, result) {
        console.log(result);
    });

似乎没有找到add函数的第二个参数，得到一个未定义的错误。考虑到队列的数量并不总是相同的，遍历所有队列元素并对集合中所有对象的每个子对象求和的最佳方法是什么?

使用这段代码，我可以一次至少执行一个队列:

var collection = db.collection('2016-08-23');
    collection.aggregate([
        {
            $group:{
                _id:'30', 
                backlog:
                {
                    $sum:'$BCNED3351.queues.30.backlog'
                },
                inflow:
                {
                    $sum:'$BCNED3351.queues.30.inflow'
                },
                handled:
                {
                    $sum:'$BCNED3351.queues.30.handled'
                }
            }
        }
    ], function(err, result) {
        console.log(result);
    });

Answer 1

如果您可以重构您的模式以遵循此设计，例如，用示例中的文档填充一个测试集合，以拥有这个流畅的重新设计的模式:

db.test.insert([
    {    
    "items": [
        {
            "key": "BCNED3351",
            "timestamp" : 1471873201170.0,
            "totalOID" : {
                "backlog" : 1405,
                "inflow" : 396,
                "handled" : 341
            },
            "queues" : [
                {
                    "key": 12,
                    "backlog" : 5,
                    "inflow" : 0,
                    "handled" : 0
                },
                {
                    "key": 30,
                    "backlog" : 124,
                    "inflow" : 1,
                    "handled" : 1
                },
                {
                    "key": 31,
                    "backlog" : 15,
                    "inflow" : 40,
                    "handled" : 29
                },
                {
                    "key": 33,
                    "backlog" : 1,
                    "inflow" : 12,
                    "handled" : 12
                },
                {
                    "key": 36,
                    "backlog" : 285,
                    "inflow" : 38,
                    "handled" : 0
                },
                {
                    "key": 40,
                    "backlog" : 1,
                    "inflow" : 1,
                    "handled" : 0
                },
                {
                    "key": 42,
                    "backlog" : 968,
                    "inflow" : 268,
                    "handled" : 267
                },
                {
                    "key": 44,
                    "backlog" : 5,
                    "inflow" : 35,
                    "handled" : 32
                },
                {
                    "key": 68,
                    "backlog" : 1,
                    "inflow" : 1,
                    "handled" : 0
                }
            ]
        }
    ]
},
{
    "items": [
        {
            "key": "PARED3100",
            "timestamp" : 1471873202167.0,
            "totalOID" : {
                "backlog" : 28,
                "inflow" : 0,
                "handled" : 0
            },
            "queues" : [
                {
                    "key": 30,
                    "backlog" : 25,
                    "inflow" : 0,
                    "handled" : 0
                },
                {
                    "key": 31,
                    "backlog" : 2,
                    "inflow" : 0,
                    "handled" : 0
                },
                {
                    "key": 36,
                    "backlog" : 1,
                    "inflow" : 0,
                    "handled" : 0
                }
            ]
        }
    ]
}
])

则可以运行以下聚合管道:

db.test.aggregate([
    { "$unwind": "$items" },
    { "$unwind": "$items.queues" },
    {
        "$group": {
            "_id": {
                "item": "$items.key",
                "queue": "$items.queues.key"
            },
            "backlog": { "$sum": "$items.queues.backlog" },
            "inflow": { "$sum": "$items.queues.inflow" },
            "handled": { "$sum": "items.queues.handled" }
        }
    }    
])

并得到结果:

/* 1 */
{
    "_id" : {
        "item" : "BCNED3351",
        "queue" : 12
    },
    "backlog" : 5,
    "inflow" : 0,
    "handled" : 0
}
/* 2 */
{
    "_id" : {
        "item" : "BCNED3351",
        "queue" : 30
    },
    "backlog" : 124,
    "inflow" : 1,
    "handled" : 0
}
/* 3 */
{
    "_id" : {
        "item" : "BCNED3351",
        "queue" : 31
    },
    "backlog" : 15,
    "inflow" : 40,
    "handled" : 0
}
/* 4 */
{
    "_id" : {
        "item" : "BCNED3351",
        "queue" : 33
    },
    "backlog" : 1,
    "inflow" : 12,
    "handled" : 0
}
/* 5 */
{
    "_id" : {
        "item" : "BCNED3351",
        "queue" : 36
    },
    "backlog" : 285,
    "inflow" : 38,
    "handled" : 0
}
/* 6 */
{
    "_id" : {
        "item" : "BCNED3351",
        "queue" : 40
    },
    "backlog" : 1,
    "inflow" : 1,
    "handled" : 0
}
/* 7 */
{
    "_id" : {
        "item" : "BCNED3351",
        "queue" : 42
    },
    "backlog" : 968,
    "inflow" : 268,
    "handled" : 0
}
/* 8 */
{
    "_id" : {
        "item" : "BCNED3351",
        "queue" : 44
    },
    "backlog" : 5,
    "inflow" : 35,
    "handled" : 0
}
/* 9 */
{
    "_id" : {
        "item" : "BCNED3351",
        "queue" : 68
    },
    "backlog" : 1,
    "inflow" : 1,
    "handled" : 0
}
/* 10 */
{
    "_id" : {
        "item" : "PARED3100",
        "queue" : 36
    },
    "backlog" : 1,
    "inflow" : 0,
    "handled" : 0
}
/* 11 */
{
    "_id" : {
        "item" : "PARED3100",
        "queue" : 31
    },
    "backlog" : 2,
    "inflow" : 0,
    "handled" : 0
}
/* 12 */
{
    "_id" : {
        "item" : "PARED3100",
        "queue" : 30
    },
    "backlog" : 25,
    "inflow" : 0,
    "handled" : 0
}