Question 220026 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – mongoDB aggregate $sum all nested fields

I have the nested fields for many ingredients and would like to add all the corresponding fields

nutrients: {
    vitaminB: {
      type: Number,
      default: 0,
    },
    vitaminC: {
      type: Number,
      default: 0,
    },
    //more

I have the following code

Ingredient.aggregate([
    {
      $match: { _id: { $in: ingredientIds } },
    },
    {
      $group: {
        _id: null,
        sums: { $sum: '$nutrients' },
      },
    },
  ]);
  console.log(stats[0].sums);

which outputs a 0. However it works if i specify a nested field for example $nutrients.vitaminC

I would expect sums to be an object with the sums of each nested field

Tags:

2

Answers


  1. 0

    There are a couple of problems with the approach that you’ve attempted. It’s also unclear if you would like to compute this sum per document or if you do want a single total across all matching documents.

    If you want the former, then you should replace the $group with a $addFields stage. This will evaluate the sum per document. One way to do that is to leverage $reduce and convert the structure of the field to something more amenable to the processing that you’re interested in. For example:

    {
    "$addFields": {
      "sums": {
        "$reduce": {
          "input": {
            "$objectToArray": "$nutrients"
          },
          initialValue: 0,
          "in": {
            $sum: [
              "$$this.v",
              "$$value"
            ]
          }
        }
      }
    }
  }

    Playground demonstration

    If you do want the latter behavior asked about above, then append a subsequent $group to pull the values together from across the documents:

    [
  {
    "$addFields": {
      "sums": {
        "$reduce": {
          "input": {
            "$objectToArray": "$nutrients"
          },
          initialValue: 0,
          "in": {
            $sum: [
              "$$this.v",
              "$$value"
            ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      sums: {
        $sum: "$sums"
      },
      
    },
    
  }
]

    Playground demonstration

    Login or Signup to reply.
  2. 0

    This should do the trick. The important step, as noted in the solution above, is to use $objectToArray to turn lvals (e.g. vitaminB:value) into rvals (e.g. k:vitaminB,v:value:

    db.foo.aggregate([
    {"$project": {"X": {"$objectToArray": "$nutrients"}}},
    {"$unwind": "$X"},

    // Not exactly sure if "default" is the number that should be summed
    // but if not, just change to the correct one.  Note the dotpath to
    // the field via the `v` field created by $objectToArray:
    {"$group": {_id: "$X.k", sums: {$sum: "$X.v.default"}}}
]);

    Yields:

    {
  _id: 'vitaminC',
  sums: 6
}
{
  _id: 'vitaminK',
  sums: 5
}
{
  _id: 'vitaminB',
  sums: 4
}
etc.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search