when i make a $group how can i return the total sum of a property present in all objects?

user_5357758 0
Viewed 33

I have a database with the following structure:

[
  {
    "provider": "provider 1",
    "debts": [
      {
        "debt": 500,
        "paid": true
      },
      {
        "debt": 900,
        "paid": false
      },
      {
        "debt": 600,
        "paid": false
      }
    ]
  },
  {
    "provider": "provider 2",
    "debts": [
      {
        "debt": 500,
        "paid": false
      }
    ]
  }
]

I want each object to store in the total field the sum of the debt field contained in the debts array (debts.debt) ONLY if thepaidproperty is false. (in my real code it works well up to here).

db.collection.aggregate(
 [
  {
    $unwind: "$debts",
  },
      {
    $group: {
      _id: "$_id",

      total: {
        $sum: {
          $cond: [{ $eq: ["$debts.paid", false] }, "debts.debt", 0],
        },
      },
    },
  }
]

how can I get the total of the sum of the total property contained in all my objects that met the above condition?

{
  "debts_providers": [
    {
      "_id": "5f29af0a17196c1624f7dd88",
      "total": 1500
    },
    {
      "_id": "5f29af0a17196c1624f7dd89",
      "total": 500
    }
  ],
  "sum_total_objects": 2000  /*900+600+500*/
}
aggregation-framework mongodb
2 Answers

You don't need to use $unwind- it's better to process an array using $reduce to sum up only unpaid debts. Then you need to $group to get a total value:

db.collection.aggregate([
    {
        $project: {
            _id: 1,
            total: {
                $reduce: {
                    input: "$debts",
                    initialValue: 0,
                    in: {
                        $add: [ "$$value", { $cond: [{ $eq: ["$$this.paid", false] }, "$$this.debt", 0] } ]
                    }
                }
            }
        }
    },
    {
        $group: {
            _id: null,
            debt_providers: { $push: "$$ROOT" },
            sum_total_objects: { $sum: "$total" }
        }
    }
])

Mongo Playground

user_62389770

You are in the right track, just need to replace root with debt_providers,

  • you missed $ in first group $cond then part, it should be $debts.debt
  • second $group that is required,
    • create new field debt_providers and push $$ROOT because documents are unwind in independent but we need to set together in debt_providers
    • create new field sum_total_objects to $sum all total value
db.collection.aggregate([
  {
    $unwind: "$debts"
  },
  {
    $group: {
      _id: "$_id",
      total: {
        $sum: {
          $cond: [
            { $eq: ["$debts.paid", false] }, 
            "$debts.debt", // corrected debts.debt to $debts.debt
            0
          ] 
        }
      }
    }
  },
  // added here
  {
    $group: {
      _id: null,
      debt_providers: { $push: "$$ROOT" },
      sum_total_objects: { $sum: "$total" }
    }
  }
])

Playground: https://mongoplayground.net/p/esRanLrIB2o

user_89871280
Related