How to $sum a specific array inside an array?

Question

Consider the following structure:

{
  "groups": [
    {
      "group_id": "A",
      "total": -1
      "items": [
        {
          "item_id": "a",
          "val": 1
        },
        {
          "item_id": "b",
          "val": 2
        }
      ]
    },
    {
      "group_id": "B",
      "total": -1
      "items": [
        {
          "item_id": "c",
          "val": 3
        },
        {
          "item_id": "d",
          "val": 4
        }
      ]
    }
  ]
}

given a group_id, I'd like to $sum the items values of this group and set the total value with that number (e.g. the total of group A is 3)

How can I do that?

I know about $unwind but I don't fully understand how to do that only to the specific array I'm interested in

turivishal · Accepted Answer · 2021-05-25 17:29:38Z

1

$map to iterate loop of groups array
$cond to check if group_id match then do sum operation otherwise nothing
$sum to get total of items.val
$mergeObjects to merge group object and new updated total field

db.collection.aggregate([
  {
    $set: {
      groups: {
        $map: {
          input: "$groups",
          in: {
            $mergeObjects: [
              "$$this",
              {
                $cond: [
                  { $eq: ["$$this.group_id", "A"] },
                  { total: { $sum: "$$this.items.val" } },
                  {}
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Playground

edited May 25, 2021 at 17:29

answered May 25, 2021 at 13:35

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

5 Comments

IsaacLevon Over a year ago

Thanks, but what if I want to evaluate total only for one specific group?

turivishal Over a year ago

you can use $cond condition there, i have updated answer.

IsaacLevon Over a year ago

Thank you so much!

IsaacLevon Over a year ago

Would you consider this query efficient? because it seems to me that this query still calculates all the totals but filters them in the $mergeObjects stage.

turivishal Over a year ago

i can not say this way is efficient, but I can say this is an efficient way than other ways.

Collectives™ on Stack Overflow

How to $sum a specific array inside an array?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related