mongoose aggregate and sum nested OBJECT (NOT ARRAY ) fields

Question

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.

Darren G · Accepted Answer · 2019-09-27 17:35:08Z

2

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

edited Sep 27, 2019 at 17:35

answered Sep 26, 2019 at 20:31

Darren G

8789 silver badges19 bronze badges

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

6 Comments

Dora Over a year ago

I suppose { $unwind: { '$meals' }}, means { $unwind: '$meals'}, right? But I get the error message of UnhandledPromiseRejectionWarning: MongoError: Unrecognized expression '$convert'

Darren G Over a year ago

$convert is new in version 4.0 of MongoDB, but that's been out for a year or so. What version are you using?

Darren G Over a year ago

If you can't update to version 4.x for whatever reason, see this post for how to convert strings to int in earlier versions: stackoverflow.com/questions/29487351/…

Dora Over a year ago

ah! no wonder I was searching around with $convert and $toInt and everyone is suggesting that, just checked the sandbox provided to play around is using mongodb 3.6x >.<" Just want to confirm what I read from the stackoverflow you sent me is right. Actually it's getting the data from db then change the value to int by using parseInt then save to db right? So totally changing the type in db?

Darren G Over a year ago

MongoDB seems to change quite a bit between versions so it can be a bit frustrating alright! Correct, the code is overwriting the data in the collection to change the type.

|

Collectives™ on Stack Overflow

mongoose aggregate and sum nested OBJECT (NOT ARRAY ) fields

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related