MongoDB Collection aggregate sum multiple objects

Question

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

Are you able to change the schema so that queues becomes an array? — chridam
– chridam, Commented Aug 23, 2016 at 11:04

chridam · Accepted Answer · 2016-08-23 11:35:09Z

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
}

Hi again chridam, I have edited the schema and it works great now with your query. Thank you so much!

Collectives™ on Stack Overflow

MongoDB Collection aggregate sum multiple objects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related