MongoDb $addFields inside arrays that $multiply two values within the array

Question

MongoDb version 3.4.4

How to aggregate a new key 'total', whose value is the product of 'course' and 'quantity' for each object inside array snapshot.

Sample document:

{
    cur: "EUR",
    snapshot: [
        {
            id: "24352345",
            course: 58.12,
            quantity: 13
        },    
        {
            id: "34552345",
            course: 18.12,
            quantity: 63
        }
    ]
}

Desired result:

{
    cur: "EUR",
    snapshot: [
        {
            id: "24352345",
            course: 58.12,
            quantity: 13,
            total: 755.56
        },    
        {
            id: "34552345",
            course: 18.12,
            quantity: 63,
            total: 1141.56
        }
    ]
}

first attempt:

db.mycoll.aggregate([{
    $addFields: {
        "snapshot.total": {
            $multiply:["$snapshot.quantity", "$snapshot.course"]
        }
    }
}])

"errmsg" : "$multiply only supports numeric types, not array"

Second attempt:

db.mycoll.aggregate( [ 
    { "$addFields": { 
        "snapshot.total": {
            "$map": { 
                "input": "$snapshot", 
                "as": "row", 
                "in": { "$multiply": [ 
                    { "$ifNull": [ "$$row.quantity", 0 ] }, 
                    { "$ifNull": [ "$$row.course", 0 ] } 
                ]} 
            }
        } 
    }} 
])

The undesired value of 'total' is an array with the totals of all the objects:

{
    cur: "EUR",
    snapshot: [
        {
            id: "24352345",
            course: 58.12,
            quantity: 13,
            total: [
                755.56,
                1141.56
            ]
        },    
        {
            id: "34552345",
            course: 18.12,
            quantity: 63,
            total: [
                755.56,
                1141.56
            ]
        }
    ]
}

chridam · Accepted Answer · 2020-01-18 15:10:39Z

16

Modify your second attempt using the $map operator to map the whole snapshot embedded document with its fields as

db.mycoll.aggregate([
    {
        "$addFields": {
            "snapshot": {
                "$map": {
                    "input": "$snapshot",
                    "as": "row",
                    "in": {
                        "id": "$$row.id",
                        "course": "$$row.course",
                        "quantity": "$$row.quantity",
                        "total": { "$multiply": [ 
                            { "$ifNull": [ "$$row.quantity", 0 ] }, 
                            { "$ifNull": [ "$$row.course", 0 ] } 
                        ]} 
                    }
                }
            }
        }
    }
])

edited Jan 18, 2020 at 15:10

answered May 13, 2017 at 10:56

chridam

104k26 gold badges246 silver badges243 bronze badges

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

1 Comment

Code Guru Over a year ago

i have an array of object. In each object there is count field that i want to multiply and then set the multiplied value as total as the new document field.

Collectives™ on Stack Overflow

MongoDb $addFields inside arrays that $multiply two values within the array

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