1

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.

Dora
  • 6,776
  • 14
  • 51
  • 99

1 Answers1

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

Darren G
  • 780
  • 8
  • 16
  • I suppose `{ $unwind: { '$meals' }},` means `{ $unwind: '$meals'},` right? But I get the error message of `UnhandledPromiseRejectionWarning: MongoError: Unrecognized expression '$convert'` – Dora Sep 26 '19 at 22:38
  • 1
    $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 Sep 27 '19 at 05:24
  • 1
    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: https://stackoverflow.com/questions/29487351/how-to-convert-string-to-numerical-values-in-mongodb – Darren G Sep 27 '19 at 05:29
  • 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? – Dora Sep 27 '19 at 16:48
  • 1
    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. – Darren G Sep 27 '19 at 17:33
  • Thanks a lot for confirmation. I believe I saw that kind of post but didn't think looping over db and change value would be a good idea so just scanned through and not expecting it's version problem :( Totally wasn't expecting such change but I tried another mongodb with version 4.0.x and your code works great :D but in case anyone reads this your comment for `_id: meals.k` is actually `_id: '$meals.k'` after I tried :D – Dora Sep 27 '19 at 17:43