Ruben Deig Ramos Ruben Deig Ramos - 3 months ago 11
Node.js Question

MongoDB Collection aggregate sum multiple objects

Given the following collection:

{
"_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
}
}
}
}


I am trying to calculate the sum of all the backlog, inflow and handled elements for each queue of every object in the collection. So far, this is what I came up for an specific queue with without sucess:

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);
});


It seems the second parameter of the add function it is not found, getting an undefined error. What is the best way to iterate through all the queues elements and make a sum of each child object for all the objects in the collection, taking into account that number of queues is not always the same?

With this code I am able to perform at least one queue at a time:

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

If you can restructure your schema to follow this design, for example populate a test collection with the documents in the sample to have this fluid redesigned schema:

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

you can then run the following aggregation pipeline:

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

and get the result:

/* 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
}