Q:

Complex aggregation query involving nested arrays

I'm moving my first steps into MongoDB but I'm having difficulties into writing complex queries.
I have several items like this in my collection:

{
    "_id" : "sku001",
    "deposits" : [ 
        {
            "deposit_id" : "deposit01",
            "total" : "3",
            "sizes" : [ 
                {
                    "size" : "36",
                    "stock" : "2"
                }, 
                {
                    "size" : "38",
                    "stock" : "0"
                }, 
                {
                    "size" : "40",
                    "stock" : "0"
                }, 
                {
                    "size" : "42",
                    "stock" : "0"
                }, 
                {
                    "size" : "44",
                    "stock" : "0"
                }, 
                {
                    "size" : "46",
                    "stock" : "1"
                }, 
                {
                    "size" : "48",
                    "stock" : "0"
                }, 
                {
                    "size" : "50",
                    "stock" : "0"
                }, 
                {
                    "size" : "52",
                    "stock" : "0"
                }
            ]
        }, 
        {
            "deposit_id" : "deposit02",
            "total" : "5",
            "sizes" : [ 
                {
                    "size" : "36",
                    "stock" : "1"
                }, 
                {
                    "size" : "38",
                    "stock" : "1"
                }, 
                {
                    "size" : "40",
                    "stock" : "0"
                }, 
                {
                    "size" : "42",
                    "stock" : "1"
                }, 
                {
                    "size" : "44",
                    "stock" : "0"
                }, 
                {
                    "size" : "46",
                    "stock" : "1"
                }, 
                {
                    "size" : "48",
                    "stock" : "1"
                }, 
                {
                    "size" : "50",
                    "stock" : "0"
                }, 
                {
                    "size" : "52",
                    "stock" : "0"
                }
            ]
        }, 
        {
            "deposit_id" : "deposit03",
            "total" : "2",
            "sizes" : [ 
                {
                    "size" : "36",
                    "stock" : "1"
                }, 
                {
                    "size" : "38",
                    "stock" : "0"
                }, 
                {
                    "size" : "40",
                    "stock" : "0"
                }, 
                {
                    "size" : "42",
                    "stock" : "1"
                }, 
                {
                    "size" : "44",
                    "stock" : "0"
                }, 
                {
                    "size" : "46",
                    "stock" : "0"
                }, 
                {
                    "size" : "48",
                    "stock" : "0"
                }, 
                {
                    "size" : "50",
                    "stock" : "0"
                }, 
                {
                    "size" : "52",
                    "stock" : "0"
                }
            ]
        }, 
        {
            "deposit_id" : "deposit04",
            "total" : "0",
            "sizes" : [ 
                {
                    "size" : "36",
                    "stock" : "0"
                }, 
                {
                    "size" : "38",
                    "stock" : "0"
                }, 
                {
                    "size" : "40",
                    "stock" : "0"
                }, 
                {
                    "size" : "42",
                    "stock" : "0"
                }, 
                {
                    "size" : "44",
                    "stock" : "0"
                }, 
                {
                    "size" : "46",
                    "stock" : "0"
                }, 
                {
                    "size" : "48",
                    "stock" : "0"
                }, 
                {
                    "size" : "50",
                    "stock" : "0"
                }, 
                {
                    "size" : "52",
                    "stock" : "0"
                }
            ]
        }
    ]
}

and would like to write a query which will output this resulting document:

{
    "_id" : "sku001",
    "total": 10,
    "sizes" [
        {
            "size" : "36",
            "stock" : "4"
        }, 
        {
            "size" : "38",
            "stock" : "1"
        }, 
        {
            "size" : "40",
            "stock" : "0"
        }, 
        {
            "size" : "42",
            "stock" : "2"
        }, 
        {
            "size" : "44",
            "stock" : "0"
        }, 
        {
            "size" : "46",
            "stock" : "2"
        }, 
        {
            "size" : "48",
            "stock" : "1"
        }, 
        {
            "size" : "50",
            "stock" : "0"
        }, 
        {
            "size" : "52",
            "stock" : "0"
        }
    ]
}

so that:

total is the sum the "total" field in each deposit
sizes is the resulting array given from the sum of the same size in each deposit

I know that I need to use aggregate() with $unwind, $group and maybe $project, but I don't understand how and in which order.
Can you help me? I'm using MongoDB version 3.4.3

A:

You may want to change your schema first before even applying the operators in the aggregation framework to produce your desired report i.e. the string values need to be parsed to numbers and then run the aggregate operation.
Changing the schema
To change the schema, you'd need to iterate the collection and for each document in the collection update the keys which need to have the string values parsed to numbers. You may want to create a temporary collection with the updated schema if it's not possible to change the original collection schema.
For the former, consider running the following update operation (for relatively small collections):
db.collection.find({}).snapshot()
  .forEach(function(doc) {
        var query = { "_id": doc._id },
            update = { "$set": {} };

        for (var i=0; i<doc.deposits.length; i++) {         
            update["$set"]["deposits."+i+".total"] =  parseInt(doc.deposits[i].total);          
            for (var j=0; j<doc.deposits[i].sizes.length; j++) {                
                update["$set"]["deposits."+i+".sizes."+j+".stock"] = parseInt(doc.deposits[i].sizes[j].stock);                              
            }
        }

        printjson(query);
        printjson(update);

        db.collection.updateOne( query, update );
  });

Aggregate Pipeline
Having the correct schema will allow you to run the pipeline in the following aggregate operation which should give you the desired report:
db.collection.aggregate([
    { "$unwind": "$deposits" },
    { "$unwind": "$deposits.sizes" },                
    {
        "$group": {
            "_id": { 
                "id": "$_id",                   
                "size": "$deposits.sizes.size"
            },
            "total_stock": { "$sum": "$deposits.sizes.stock" },             
            "deposits": { 
                "$push": {
                    "deposit_id": "$deposits.deposit_id",
                    "total":  "$deposits.total" 
                }
            }
        }
    },
    { "$unwind": "$deposits" },
    {
        "$group": {
            "_id": { 
                "id": "$_id.id", 
                "deposit_id": "$deposits.deposit_id"
            },
            "sizes": { 
                "$push": {
                    "size": "$_id.size",
                    "stock": "$total_stock"
                }
            },
            "total": { "$first": "$deposits.total" }
        }
    },      
    {
        "$group": {
            "_id": "$_id.id",
            "total": { "$sum": "$total" },
            "sizes": { "$first": "$sizes" }    
        }
    }
])

