MongoDB - Sum value in nested array

Question

I have an object looks like this

{
    "_id" : {
        "import_type" : "MANUAL_UPLOAD",
        "supplier" : "jabino.de",
        "unit_price" : "0"
    },
    "statuses" : [ 
        {
            "status" : "DUPLICATED",
            "count" : 14
        }, 
        {
            "status" : "BLACKLISTED",
            "count" : 2
        }, 
        {
            "status" : "USABLE",
            "count" : 2239
        }, 
        {
            "status" : "INVALID_EMAIL_ADDRESS",
            "count" : 1
        }, 
        {
            "status" : "DUPLICATED",
            "count" : 14
        }, 
        {
            "status" : "BLACKLISTED",
            "count" : 2
        }, 
        {
            "status" : "USABLE",
            "count" : 2239
        }, 
        {
            "status" : "INVALID_EMAIL_ADDRESS",
            "count" : 1
        }
    ]
}

How I can sum all the count in the statuses array which has the same status without losing keys-values in _id. E.g. in this case

Duplicated: 28
Blacklisted: 4
Usable: 4478
Invalid email address: 2

Ashh · Accepted Answer · 2018-12-19 09:40:10Z

5

You can use below aggregation

db.collection.aggregate([
  { "$unwind": "$statuses" },
  { "$group": {
    "_id": {
      "_id": "$_id",
      "statuses": "$statuses.status"
    },
    "count": { "$sum": "$statuses.count" }
  }},
  { "$group": {
    "_id": "$_id._id",
    "statuses": {
      "$push": {
        "status": "$_id.statuses",
        "count": "$count"
      }
    }
  }}
])

edited Dec 19, 2018 at 9:40

answered Dec 19, 2018 at 9:38

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

2 Comments

Tri Nguyen Over a year ago

Works. Thanks a lot.

Tri Nguyen Over a year ago

2 more mins mate

Collectives™ on Stack Overflow

MongoDB - Sum value in nested array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related