Multi tool use

In Db I have some sample data:

Object 1

"_id" : ObjectId("5b5934bb49b")
"payment" : {
"paid_total" : 500,
"name" : "havi",
"payment_mode" : "cash",
"pd_no" : "PD20725001",
"invoices" : [
{
"invoice_number" : "IN11803831583"
}
],
"type" : "Payment"
}


Object 2

"_id" : ObjectId("5b5934ee31e"),
"patient" : {
"invoice_date" : "2018-07-26",
"invoiceTotal" : 2000,
"pd_no" : "PD20725001",
"type" : "Invoice",
"invoice_number" : "IN11803831583"
}


Note: All the Data is In same Collection

As the above shown data I have many objects in my database. How can I get the Sum from the data above of invoiceTotal and sum of paid_total and then subtract the paid_total from invoiceTotal and show the balance amount for matching pd_no and invoice_number.

invoiceTotal

paid_total

paid_total

invoiceTotal

pd_no

invoice_number

The output I expect looks like

invoiceTotal : 2000
paid_total : 500
Balance : 1500


2 Answers
2

Sample Input :

{
"_id" : ObjectId("5b596969a88e07f00d6dac17"),
"payment" : {
"paid_total" : 500,
"name" : "havi",
"payment_mode" : "cash",
"pd_no" : "PD20725001",
"invoices" : [
{
"invoice_number" : "IN11803831583"
}
],
"type" : "Payment"
}
}
{
"_id" : ObjectId("5b596986a88e07f00d6dac18"),
"patient" : {
"invoice_date" : "2018-07-26",
"invoiceTotal" : 2000,
"pd_no" : "PD20725001",
"type" : "Invoice",
"invoice_number" : "IN11803831583"
}
}


Use this aggregate query :

db.test.aggregate([
{
$project : {
_id : 0,
pd_no : { $ifNull: ["$payment.pd_no", "$patient.pd_no" ] },
invoice_no : { $ifNull: [ { $arrayElemAt : ["$payment.invoices.invoice_number", 0] },"$patient.invoice_number" ] },
type : { $ifNull: [ "$payment.type", "$patient.type" ] },
paid_total : { $ifNull: [ "$payment.paid_total", 0 ] },
invoice_total : { $ifNull: [ "$patient.invoiceTotal", 0 ] },
}
},
{
$group : {
_id : {
pd_no : "$pd_no",
invoice_no : "$invoice_no"
},
paid_total : {$sum : "$paid_total"},
invoice_total : {$sum : "$invoice_total"}
}
},
{
$project : {
_id : 0,
pd_no : "$_id.pd_no",
invoice_no : "$_id.invoice_no",
invoice_total : "$invoice_total",
paid_total : "$paid_total",
balance : {$subtract : ["$invoice_total" , "$paid_total"]}
}
}
])


In this query we are first finding the pd_no and invoice_no, which we are then using to group the documents. Next, we are getting the invoice_total and paid_total and then subtracting them to get the balance.

pd_no

invoice_no

invoice_total

paid_total

balance

Output :

{
"pd_no" : "PD20725001",
"invoice_no" : "IN11803831583",
"invoice_total" : 2000,
"paid_total" : 500,
"balance" : 1500
}


I assume that you will only have documents with invoiceTotal or paid_total and never both at the same time.

invoiceTotal

paid_total

you need first to get an amount to get the balance so if paid total it needs to be negative and positive on the case of the invoice total, and you can do this by using first the $project on the pipeline.

collection.aggregate([
{
$project : {
'patient.invoiceTotal': 1,
'payment.paid_total': 1,
ammount: {
$ifNull: ['$patient.invoiceTotal', { $multiply: [-1, '$payment.paid_total']}]
}
}
},
{
$group: {
_id: 'myGroup',
invoiceTotal: { $sum: '$patient.invoiceTotal' },
paid_total: { $sum: '$payment.paid_total' },
balance: { $sum: '$ammount' }
}
}
])


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.