MongoDB Aggregation - Sum value of nested arrays

Question

I'm trying to get the sum of a specific field that's inside an array. The problem is, that array is also inside another array. My documents are as follow (simplified):

{
    "nome": "test-1"
    "notas": [{
        "numero_fiscal": "0001",
        "valores": {
            "portal": 1000,
            "arquivo": 1000
        },
        "abatimentos": [{
            "valor": 250,
            "tipo": "TIPO 1"
        }, {
            "valor": 250,
            "tipo": "TIPO 2"
        }]
    }, {
        "numero_fiscal": "0002",
        "valores": {
            "portal": 2000,
            "arquivo": 2000
        },
        "abatimentos": [{
            "valor": 500,
            "tipo": "TIPO 1"
        }, {
            "valor": 500,
            "tipo": "TIPO 2"
        }]
    }]
}

I want my output to be something like:

{
    "_id": "resumo",
    "total_notas": 2, // this is the counter for the array "notas"
    "soma_portal": 3000, // this is the sum of the "valores.portal" of all "notas"
    "soma_arquivo": 3000, // this is the sum of the "valores.arquivo" of all "notas"
    "soma_abatimento": 1500 // this is the sum of all the "abatimentos.valor" from all the "notas"
}

I have tried the following aggregation (amongst others, but the result is always the same):

Notas.aggregate()
.match(my_query)
.unwind('notas') // unwinding 'notas' array
.group({
    '_id': 'resumo',
    'total_notas': { '$sum': 1 },
    'abatimentos': { '$push': '$notas.abatimentos' },
    'soma_portal': { '$sum': { '$notas.valores.portal' } },
    'soma_arquivo': { '$sum': { '$notas.valores.arquivo' } }
})
.unwind('$abatimentos')
.group({
    '_id': '$_id',
    'total_notas': { '$first': '$total_notas' },
    'soma_portal': { '$first': '$soma_portal' },
    'soma_arquivo': { '$first': '$soma_arquivo' },
    'soma_abatimento': { '$sum': '$abatimentos.valor' }
})

This gives me almost everything i want correctly, but the 'soma_abatimento' is always 0, instead of 1500

Am i on the right path? Or is this something that should not be done on the database query?

tkbyte · Accepted Answer · 2017-07-27 23:03:44Z

In your current version, you need to add a double $unwind because, as you've correctly identified, after your first $group you have an array within an array.

Here is a slightly modified version that produces the expected result.

db.Notas.aggregate([{
  $unwind: "$notas"
}, {
  $group: {
    _id: "resumo",
    total_notas: { $sum: 1 },
    soma_portal: { $sum: "$notas.valores.portal" },
    soma_arquivo: { $sum: "$notas.valores.arquivo" },
    abatimentos: { $push: "$notas.abatimentos" }
  }
}, {
  $unwind: "$abatimentos"
}, {
  $unwind: "$abatimentos"
}, {
  $group: {
    _id: "$_id",
    total_notas: { $first: "$total_notas" },
    soma_portal: { $first: "$soma_portal" },
    soma_arquivo: { $first: "$soma_arquivo" },
    soma_abatimentos: { $sum: "$abatimentos.valor" }
  }
}])

If you're able to use MongoDB >= 3.4, I'd recommend a different approach that will reduce document size and obviate the need for more than one $unwind using $reduce that was added in MongoDB 3.4

db.Notas.aggregate([{
  $unwind: "$notas"
}, {
  $project: {
    _id: 0,
    portal: "$notas.valores.portal",
    arquivo: "$notas.valores.arquivo",
    abatimentos: {
      $reduce: {
        input: "$notas.abatimentos",
        initialValue: 0,
        in: { $add: ["$$value", "$$this.valor"] }
      }
    }
  }
}, {
  $group: {
    _id: "resumo",
    total_notas: { $sum: 1 },
    soma_portal: { $sum: "$portal" },
    soma_arquivo: { $sum: "$arquivo" },
    soma_abatimentos: { $sum: "$abatimentos" }
  }
}])

Thanks tkbyte, that worked perfectly! The MongoDB >= 3.4 is better, so thank you for providing both ways.

Doyeon Kim · Accepted Answer · 2024-03-07 14:35:11Z

1

'$notas.abatimentos.valor' projects [[250, 250], [500, 500]]. so you can use $map.

{
  $project: {
    abatimentos: {
      $sum: {
        $map: {
          input: '$notas.abatimentos.valor',
          in: { $sum: '$$this' }
        }
      }
    }
  }
}

answered Mar 7, 2024 at 14:35

Doyeon Kim

111 bronze badge

Collectives™ on Stack Overflow

MongoDB Aggregation - Sum value of nested arrays

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related