How to write group by query for embedded array of document in mongodb

Question

I have a collection in below format

{customerID:1,acctDetails:[{accType:"Saving",balance:100},{accType:"checking",balance:500}]}
{customerID:2,acctDetails:[{accType:"Saving",balance:500}]}

I want to find total balance by acctType. I tried below query.

db.<collectionName>.aggregate([{$group:{_id:"$acctDetails.accType",totalBalance:{$sum:"$accDetails.balace"}}}])

But it is not giving right result.

Does this answer your question? MongoDB group by array inner-elements or (stackoverflow.com/questions/33134523/…) - you need to $unwind first ! — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Jul 29, 2020 at 14:32

Pedro Henrique Cardoso · Accepted Answer · 2020-07-29 17:01:24Z

3

I think that this might solve your problem. You first need to use $unwind to transform each array element in a document, then use $group to sum the total balance by account type.

db.collection.aggregate([
  {"$unwind": "$acctDetails"},
  {
    "$group": {
      "_id": "$acctDetails.accType",
      "totalBalance": {"$sum": "$acctDetails.balance"}
    }
  }
])

Working Mongo playground

edited Jul 29, 2020 at 17:01

answered Jul 29, 2020 at 15:01

Pedro Henrique Cardoso

4605 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

How to write group by query for embedded array of document in mongodb

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related