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.

turivishal · Accepted Answer · 2020-12-11 05:51:08Z

2

$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

answered Dec 11, 2020 at 5:51

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

3 Comments

Ritik Saxena Over a year ago

I get this error when I try to run this query on actual data - $arrayToObject requires an object with keys 'k' and 'v', where the value of 'k' must be of type string. Found type: null

turivishal Over a year ago

there would be some null or empty value in title or val fields. can you make sure where is it?

Ritik Saxena Over a year ago

figured out. error was from my end. thanks!! :)

ray · Accepted Answer · 2022-11-10 16:15:03Z

1

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.

edited Nov 10, 2022 at 16:15

answered Dec 11, 2020 at 5:31

ray

15.6k12 gold badges96 silver badges104 bronze badges

3 Comments

Ritik Saxena Over a year ago

thanks!! is there a way to achieve this when we do not know the field values High, Medium, Low by segregating based on unique title values?

turivishal Over a year ago

@Ray that was too old post, for the latest version it is possible, i have just answered there take a look

ray Over a year ago

@turivishal yes you are right, your answer should be the accepted one :)

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related