Mongodb Aggregate query to sum all values based on fields inside objects in an Array

Question

I have a collection data in MongoDB which looks like this -

document 1-

{
    metadata:[
        {
            "title": "High",
            "val": 12
        },
        {
            "title": "Medium",
            "val": 15
        },
        {
            "title": "Low",
            "val": 2
        }
    ]
}

document2 -

{
    metadata:[
        {
            "title": "High",
            "val": 10
        },
        {
            "title": "Medium",
            "val": 12
        },
        {
            "title": "Low",
            "val": 20
        }
    ]
}

& so on..

I wish to aggregate the val field based on the title value. The output should look like -

{
    "High": 22,
    "Medium": 27,
    "Low": 22
}

How do I achieve this? Thanks in advance.

Answer 1

• $unwind deconstruct metadata array
• $grpup by title and make sum of val
• $group by null, convert array to object from key and value by $arrayToObject, and merge objects using $mergeObjects

db.collection.aggregate([
  { $unwind: "$metadata" },
  {
    $group: {
      _id: "$metadata.title",
      sum: { $sum: "$metadata.val" }
    }
  },
  {
    $group: {
      _id: null,
      metadata: {
        $mergeObjects: {
          $arrayToObject: [
            [{ k: "$_id", v: "$sum" }]
          ]
        }
      }
    }
  }
])

Playground

Answer 2

You can first $unwind the metadata array. Then use $group and $cond to $sum your values.

db.collection.aggregate([
  {
    "$unwind": "$metadata"
  },
  {
    $group: {
      _id: null,
      High: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$metadata.title",
                "High"
              ]
            },
            "$metadata.val",
            0
          ]
        }
      },
      Medium: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$metadata.title",
                "Medium"
              ]
            },
            "$metadata.val",
            0
          ]
        }
      },
      Low: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$metadata.title",
                "Low"
              ]
            },
            "$metadata.val",
            0
          ]
        }
      }
    }
  }
])

Here is the mongo playground for your reference.