Sum in nested document MongoDB

Question

I'm trying to sum some values in an array of documents, with no luck.

This is the Document

db.Cuentas.find().pretty()

{
    "Agno": "2013",
    "Egresos": [
        {
            "Fecha": "28-01-2013",
            "Monto": 150000,
            "Detalle": "Pago Nokia Lumia a @josellop"
        },
        {
            "Fecha": "29-01-2013",
            "Monto": 4000,
            "Detalle": "Cine, Pelicula fome"
        }
    ],
    "Ingresos": [],
    "Mes": "Enero",
    "Monto": 450000,
    "Usuario": "MarioCares"
    "_id": ObjectId(....)
}

So, i need the sum of all the "Monto" in "Egresos" for the "Usuario": "MarioCares". In this example 154000

Using aggregation i use this:

db.Cuentas.aggregate(
    [
        { $match: {"Usuario": "MarioCares"} },
        { $group: 
            {
                _id: null,
                "suma": { $sum: "$Egresos.Monto" }
            }
        }
    ]
)

But i always get

{ "result" : [{ "_id" : null, "suma" : 0 }], "ok" : 1 }

What am i doing wrong ?

P.D. already see this and this

I believe you want to unwind the Egresos array first and then group on null and sum — Sammaye
– Sammaye, Commented Jan 28, 2013 at 20:27
@Sammaye added {"$unwind": $Egresos } and then i got ReferenceError: Egresos is not defined — Mario Cares Cabezas
– Mario Cares Cabezas, Commented Jan 28, 2013 at 20:35
You can simplify to use below code in latest versions for summing array values in a single document.db.Cuentas.aggregate([ {$match: {"Usuario": "MarioCares"} }, {$project: { "suma": {$sum: "$Egresos.Monto" }}} ]) — s7vr
– s7vr, Commented Dec 13, 2017 at 22:00

JohnnyHK · Accepted Answer · 2013-01-28 20:34:04Z

29

As Sammaye indicated, you need to $unwind the Egresos array to duplicate the matched doc per array element so you can $sum over each element:

db.Cuentas.aggregate([
    {$match: {"Usuario": "MarioCares"} }, 
    {$unwind: '$Egresos'}, 
    {$group: {
        _id: null, 
        "suma": {$sum: "$Egresos.Monto" }
    }}
])

answered Jan 28, 2013 at 20:34

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

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

Comments

Imdad · Accepted Answer · 2019-02-21 18:17:02Z

11

You can do also by this way. don't need to group just project your fields.

db.Cuentas.aggregate([
    { $match: { "Usuario": "MarioCares" } },
    {
        $project: {
            'MontoSum': { $sum: "$Egresos.Monto" }
        }
    }
])

edited Feb 21, 2019 at 18:17

Imdad

7691 gold badge12 silver badges32 bronze badges

answered Jan 10, 2018 at 11:10

Aabid

9515 silver badges24 bronze badges

4 Comments

Aabid Over a year ago

Can you give example or snippet?

Daga Arihant Over a year ago

I AM SORRY @Aabid . It did not work because i was having a little different scenario in which lets say the Egresos was also a variable in the same project stage... But it does work in the scenario of this question..

Aabid Over a year ago

@DagaArihant So this worked for the question you asked?

Daga Arihant Over a year ago

I am sorry, My case was different and i did not ask question.

nimrod serok · Accepted Answer · 2022-06-05 10:34:29Z

0

Since mongoDB version 3.4 you can use $reduce to sum array items:

db.collection.aggregate([
  {
    $match: {Usuario: "MarioCares"}
  },
  {
    $project: {
      suma: {
        $reduce: {
          input: "$Egresos",
          initialValue: 0,
          in: {$add: ["$$value", "$$this.Monto"]}
        }
      }
    }
  }
])

Playground example

answered Jun 5, 2022 at 10:34

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Collectives™ on Stack Overflow

Sum in nested document MongoDB

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related