Use $multiply on nested fields in aggregation in MongoDB

Question

I am trying to aggregate in MongoDB.

I have a collection with some items. Each item has an array rows and each object in rows has fields quantity and price.

I want to multiply quantity and price, but I don't know how to specify the fields correctly.

I have tried

const pipeline = [
  {
    $group: {
      _id: {
        number: '$number',
      },
      total: {
        $sum: {
          $multiply: [
            '$rows.quantity',
            '$rows.price'
          ]
        }
      },
    }
  }
];

but it says that $multiply only supports numeric types and not arrays.

So it seems it doesn't understand that $rows.quantity is the numeric type field quantity in each object in the array.

I guess I should probably use $each or something else in order to iterate through the objects in the array.

From Using multiply aggregation with MongoDB I see that I am specifying the fields correctly; however, in that example it is a nested object instead of an array, so maybe I have to use https://docs.mongodb.org/v3.0/reference/operator/aggregation/unwind/?

Sample document

{
  number: 2,
  rows: [
    {
      quantity: 10,
      price: 312
    },
    {
      quantity: 10,
      price: 312
    },
    {
      quantity: 10,
      price: 312
    },
  ]
}

You can try $unwind which splits arrays and then you can try $multiple. Here is the link. docs.mongodb.org/v3.0/reference/operator/aggregation/unwind. If you can paste the sample document, I will try and let you know the answer. — harshavmb
– harshavmb, Commented Jan 1, 2016 at 13:07

Sede · Accepted Answer · 2016-01-02 15:36:09Z

11

Using the .aggregate() method.

Starting in version 3.2 you can use the $sum accumulator operator in the $project stage to calculates and returns the sum of array of quantity * price. Of course to get the array you need to use the $map operator. The $ifNull operator evaluates the value of "quantity" and "price" then returns 0 if they evaluate to a null value. The last stage in the pipeline is the $group stage where you group your document by "number" and return the "total" for each each group.

db.collection.aggregate([
    { "$project": { 
        "number": 1,  
        "total": { 
            "$sum": { 
                "$map": { 
                    "input": "$rows", 
                    "as": "row", 
                    "in": { "$multiply": [ 
                        { "$ifNull": [ "$$row.quantity", 0 ] }, 
                        { "$ifNull": [ "$$row.price", 0 ] } 
                    ]} 
                }
            }
        }
    }},
    { "$group": { 
        "_id": "$number", 
        "total": { "$sum": "$total" } 
    }}
])

If you are not on version 3.2 you will need to denormalize the "rows" array before the $project stage using the $unwind operator.

db.collection.aggregate([
    { "$unwind": "$rows" }, 
    { "$project": { 
        "number": 1,  
        "value": { "$multiply": [
            { "$ifNull": [ "$rows.quantity", 0 ] }, 
            { "$ifNull": [ "$rows.price", 0 ] } 
        ]}
    }}, 
    { "$group": { 
        "_id": "$number", 
        "total": { "$sum": "$value" } 
    }}
])

edited Jan 2, 2016 at 15:36

answered Jan 1, 2016 at 13:44

Sede

61.5k20 gold badges158 silver badges162 bronze badges

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

2 Comments

Jamgreen Over a year ago

Thank! It seems it makes some errors if either quantity or price is not set. Can I do something to ensure that quantity and price are always evaluated as numbers (i.e. if not set, just treat them as zero)? Or should I just skip rows where either quantity or price are zero, since they wont add to the total anyway?

Sede Over a year ago

@Jamgreen you need to use the $ifNull operator to return 0 if "quantity" or price is missing. I've updated the answer. Feel free to accept the answer if it helped.

Collectives™ on Stack Overflow

Use $multiply on nested fields in aggregation in MongoDB

Sample document

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Sample document

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related