CODEWITHSUNDEEP
mongodbaggregation-frameworkmatch
Anjansai
Anjansai

Reputation: 47

In Mongodb How to Give two different $match

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.

The output I expect looks like

invoiceTotal : 2000
paid_total : 500
Balance : 1500

Upvotes: 4

Views: 73

Answers (2)

Subhashree Pradhan
Subhashree Pradhan

Reputation: 812

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.

Output :

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

Upvotes: 3

dege
dege

Reputation: 2954

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

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'  }
        }
}
])

Upvotes: 0

Related Questions