CODEWITHSUNDEEP
mongodbmongoosesumaggregate
Dora
Dora

Reputation: 6988

mongoose aggregate and sum nested OBJECT (NOT ARRAY ) fields

I have seen lots posts to using aggregate to sum nested ARRAY fields, I tried using it with my nested object which did not work.

When I query, the data structure is something like...

[
        {
            "key": "value",
            "more_key": "more_value",
            "meals": {
                "A": {
                    "name": "salmon",
                    "amount": "8"
                },
                "B": {
                    "name": "vege",
                    "amount": "6"
                },
            }
        },
        {
            "key": "value",
            "more_key": "more_value",
            "meals": {
                "A": {
                    "name": "salmon",
                    "amount": "8"
                },
                "B": {
                    "name": "vege",
                    "amount": "6"
                },
                "C": {
                    "name": "other meal",
                    "amount": "6"
                },
            }
        },
    ];

I am trying to sum the amount

I have tried something like this...

await Model.aggregate([
        { $match: { isDeleted: false } },
        { $unwind: '$meals' },  // tried with + without this
        { $group: { _id: null, sum: { $sumA: '$meals.A.amount', $sumB: '$meals.B.amount' } } }
    ]);

Can someone give me some advice and suggestion on how this can be done?

Thanks in advance.

Upvotes: 1

Views: 701

Answers (1)

Darren G
Darren G

Reputation: 878

There are a few things going on here:

1) $unwind doesn't work on objects, only on arrays. You can fix this by converting your meals object to an array with $objectToArray

2) Your amount field looks like it is of type String so will need to be converted to a number for summing

Here is an aggregation that does what you need:

await Model.aggregate([
  // Convert meals object to array
  { $project: { meals: { $objectToArray: '$meals' }}},

  //  unwind meals array
  { $unwind:  '$meals' },

  // convert meals.v.amount to integer and sum them (the v is shorthand for values)
  { $group: { 
    _id: null, 
    total: {
      $sum: { 
        $convert: {
          input: '$meals.v.amount',
          to: 'int'
        }
      }
    }
  }}
])

You could change _id of $group to _id: meals.k to sum by the keys of the meals object i.e A, B, C

Upvotes: 2

Related Questions