MongoDB + join query , group by with count

Viewed 317

I have payments collection(table) and data have based on branches payment..

branch details have in branch collections.

Sample data in payment collections:

[
  {
    "amount" : 1000,
    "paymentStatus" : "Unpaid",
    "branchId" : ObjectId("5b320de5a79ae855912eb332"),
    "userId" : ObjectId("5b320de5a79ae855912eb333"),
    "receivedAmount" : 500,
  },
  {
    "amount" : 5000,
    "paymentStatus" : "paid",
    "branchId" : ObjectId("5b320de5a79ae855912e3335"),
    "userId" : ObjectId("5b320de5a79443855912eb332"),
    "receivedAmount" : 5000,
  },
  {
    "amount" : 2000,
    "paymentStatus" : "paid",
    "branchId" : ObjectId("5b320de5a79ae855912eb338"),
    "userId" : ObjectId("5b320de5a79ae855912eb432"),
    "receivedAmount" : 2000,
  }
]

I am trying to fetch data based on below conditions

  1. group by branchId,
  2. Total Amount
  3. Total receivedAmount,
  4. total Users
  5. Total Paid users count.
  6. branch details

my query:

> db.payments.aggregate([ { $group: { _id : { branchId : '$branchId'}, amount: {$sum: '$amount'}, receivedAmount : {$sum : '$receivedAmount'}, totalStudents: {$sum :1} }}])

current output:

[{ "_id" : { "branchId" : ObjectId("5b320de5a79ae855912eb377") }, "amount" : 65148, "receivedAmount" : 13276, "totalStudents" : 33 }
{ "_id" : { "branchId" : ObjectId("5a992ae104329d5359be6980") }, "amount" : 9440, "receivedAmount" : 0, "totalStudents" : 4 }
]

expected output:

[{ "branchId" : ObjectId("5b320de5a79ae855912eb377") , "amount" : 65148, "receivedAmount" : 13276, "totalStudents" : 33, "paidStudents": 2 , "branchName": 'abcd'}
{ "_id" : { "branchId" : ObjectId("5a992ae104329d5359be6980") , "amount" : 9440, "receivedAmount" : 0, "totalStudents" : 4, "paidStudents": 2 , "branchName": 'xyz'}
]

Questions, how to fetch count only for paid status and join to branch tables to get branch name.

0 Answers
Related