Ho use $sum (aggregation) for array of object and check greater than for each sum-CodePudding

My document structure is as follow :

{
    "_id" : ObjectId("621ccb5ea46a9e41768e0ba8"),
    "cust_name" : "Anuj Kumar",
    "product" : [
        {
            "prod_name" : "Robot",
            "price" : 15000
        },
        {
            "prod_name" : "Keyboard",
            "price" : 65000
        }
    ],
    "order_date" : ISODate("2022-02-22T00:00:00Z"),
    "status" : "processed",
    "invoice" : {
        "invoice_no" : 111,
        "invoice_date" : ISODate("2022-02-22T00:00:00Z")
    }
}

How to do the following query... List the details of orders with a value >10000.

I want to display only those objects whose sum of prices is greater than 10000

I try this

db.order.aggregate([{$project : {sumOfPrice : {$sum : "$product.price"} }}])

Output

{ "_id" : ObjectId("621ccb5ea46a9e41768e0ba8"), "sumOfPrice" : 80000 }
{ "_id" : ObjectId("621ccba9a46a9e41768e0ba9"), "sumOfPrice" : 16500 }
{ "_id" : ObjectId("621ccbfaa46a9e41768e0baa"), "sumOfPrice" : 5000 }

I want to check this sumOfPrice is greater than 10000 or not and display those order full object.

CodePudding user response：

You can just add a $match stage right after that checks for this conditions, like so:

db.collection.aggregate([
  {
    $addFields: {
      sumOfPrice: {
        $sum: "$product.price"
      }
    }
  },
  {
    $match: {
      sumOfPrice: {
        $gt: 10000
      }
    }
  }
])

Mongo Playground