aggregation nested Array element itself in mongodb

Question

Is it possible to aggregate nested array element itself in Mongodb? e.g. the raw data is

{"transId" : "12345","customer" : "cust1", "product" : [{"type" : "cloth","price" : 100},{"type" : "toy","price" : 200}]}
{"transId" : "45672","customer" : "cust1", "product" : [{"type" : "cloth","price" : 10},{"type" : "toy","price" : 500}]}
{"transId" : "99999","customer" : "cust2", "product" : [{"type" : "cloth","price" : 40},{"type" : "toy","price" : 5}]}

I would like each of the nested array element is aggregated according to the type by customer e.g.

Result:

{"customer" : "cust1", "product" : [{"type" : "cloth","price" : 110},{"type" : "toy","price" : 700}]}
{"customer" : "cust2", "product" : [{"type" : "cloth","price" : 40},{"type" : "toy","price" : 5}]}

Could you please help to show me how to do that? Thanks.

Sede · Accepted Answer · 2016-03-28 11:55:16Z

2

You can do this using the aggregation framework. You need to denormalize the "product" array using $unwind operation. From there you need two $group stage. In the first group stage, you group your documents by _id which in your case must be a compound field, and use the $sum accumulator operator to return the sum of price. In the last $group stage you use the $push accumulator operator to return an array of "product".

db.customers.aggregate([ 
    // Denormalize the product array
    { "$unwind": "$product" }, 
    // Group your documents by `_id`
    { "$group": { 
        "_id": { "customer": "$customer", "type": "$product.type" }, 
        "price": { "$sum": "$product.price" } 
    }}, 
    // reconstruct the "product" array.
    { "$group": { 
        "_id": "$_id.customer", 
        "product": { "$push": { "type": "$_id.type", "price": "$price" } } 
    }}
])

Which returns:

{
        "_id" : "cust1",
        "product" : [
                {
                        "type" : "toy",
                        "price" : 700
                },
                {
                        "type" : "cloth",
                        "price" : 110
                }
        ]
}
{
        "_id" : "cust2",
        "product" : [
                {
                        "type" : "toy",
                        "price" : 5
                },
                {
                        "type" : "cloth",
                        "price" : 40
                }
        ]
}

answered Mar 28, 2016 at 11:55

Sede

61.5k20 gold badges158 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saleem Over a year ago

One addition. append {$project:{_id:0, customer:"$_id", product:1}} to aggregation pipeline to conform with OP desired format.

Sede Over a year ago

@Saleem No! adding $project will cause a drop of performance.

Collectives™ on Stack Overflow

aggregation nested Array element itself in mongodb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related