Using Sum with Last mongodb

Question

UseCase: I have the following data:

{"accountNumber":"1-1", "details":["version":{ "number": "1","accountGroup":"1", "editable":"false" , "amount":100 }]}
{"accountNumber":"1-2", "details":[version":{ "number": "2", "accountGroup":"1", "editable":"false" ,  "amount":200}]}
{"accountNumber":"2-1", "details":[version":{ "number": "1", "accountGroup":"2", "editable":"false",  "amount":200 }]}

Where: my document is account. Each record has a accountGroup (1, 2). A group can have multiple versions. AccountNumber is being initialized by the combination of AccountGroup & version

I want to get the latest version of the account (accountNumber 1-2 & 2-1) along with the sum of their amount.

Expected output:

{accountNumber:2-1}, {accountNumber: 1-2}, total: 400 (sum of amount of the latest versions of the account group)

I am using the following query:

db.getCollection('account').aggregate([
{ "$sort": { "accountNumber": 1 } },
{ "$unwind": "$details"},
 { "$group": {    

     "_id": "$details.version.accountGroup",
    "Latestversion": { "$last": "$$ROOT" },
    "total": { 
        $sum: "$details.version.amount" 
    } 
}

}])

It gets the sum of the all the versions which belongs to a group.

Current output:

{"accountNumber": "1-2", total: 300}, {"accountNumber":"2-1", total: 200}

I am new to Mongodb, any help is appreciated. Looking forward for a response.

s7vr · Accepted Answer · 2017-05-18 13:21:01Z

1

You will need two $group stages.

First $group to find the latest document for each account group and second $group to sum amount from latest document.

Something like

aggregate([
  { "$sort": { "accountNumber": 1 } },
  { "$unwind": "$details"},
  { "$group": {    
      "_id": "$details.version.accountGroup",
       "latest": { "$last": "$$ROOT" }
    }
  },
  { "$group": {    
       "_id": null,
       "accountNumbers": { $push:"$latest.accountNumber" },
       "total": { $sum: "$latest.details.version.amount" } 
    }
  }
])

You can update your structure to below and remove $unwind.

{"accountNumber":"1-1", detail:{"number": "1","accountGroup":"1", "editable":"false" , "amount":100 }}

answered May 18, 2017 at 13:21

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

1 Comment

Nitish Bhardwaj Over a year ago

Thanks, this rocks.

Collectives™ on Stack Overflow

Using Sum with Last mongodb

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related