mongodb - 在 Mongodb 中如何给出两个不同的 $match

Question

在 Db 中我有一些示例数据:

对象 1

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

对象 2

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

注意:所有数据都在同一个集合中

如上图所示，我的数据库中有很多对象。如何从 invoiceTotal 和 paid_total 的总和中获取 Sum，然后从 invoiceTotal 中减去 paid_total > 并显示匹配 pd_no 和 invoice_number 的余额。

我期望的输出看起来像

invoiceTotal : 2000
paid_total : 500
Balance : 1500

Answer 1

示例输入:

{
    "_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"
    }
}

使用这个聚合查询:

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

在此查询中，我们首先找到 pd_no 和 invoice_no，然后我们使用它们对文档进行分组。接下来，我们获取 invoice_total 和 paid_total，然后将它们相减以获得 balance。

输出:

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