MongoDB聚合查询修改：根据amount值生成Debit/Credit字段

需求说明 #

将现有聚合结果中的amount字段替换为Debit和Credit字段，规则如下：

• 当amount为正数时，Debit取值为该正数，Credit取值为0
• 当amount为负数时，Credit取值为其绝对值，Debit取值为0
  同时保留原有的count和fields字段。

当前聚合查询 #

[
  {$unwind: '$journals'},
  {$group: {
    _id : { accountCode : '$journals.accountCode', accountName :'$journals.accountName' },
    amount : { $sum : { $multiply :['$journals.journalAmount', '$journals.sign']}},
    count : {$sum : 1}
  }},
  {$addFields: {fields : '$_id'}},
  {$project : {_id : 0}}
]

现有聚合结果 #

{
"count": 1,
"fields": {
"accountCode": "17G00011",
"accountName": "GST input Receivable"
},
"amount": 94.5
}
{
"amount": -619.5,
"count": 1,
"fields": {
"accountCode": "21C00005",
"accountName": "Cyberview Technologies"
}
}
{
"amount": 525,
"count": 1,
"fields": {
"accountCode": "13I00002",
"accountName": "Inventory Control A/c"
}
}

期望结果 #

{
  "count": 1,
  "fields": {
    "accountCode": "17G00011",
    "accountName": "GST input Receivable"
  },
  "Debit": 94.5,
  "Credit" : 0
}
{
  "Credit": 619.5,
  "Debit" : 0,
  "count": 1,
  "fields": {
    "accountCode": "21C00005",
    "accountName": "Cyberview Technologies"
  }
}
{
  "Debit": 525,
  "Credit" : 0,
  "count": 1,
  "fields": {
    "accountCode": "13I00002",
    "accountName": "Inventory Control A/c"
  }
}

原始文档 #

[
    {
        "_id": "6656bdaddc2f78dc3f743a3f",
        "voucherNumber": "12",
        "voucherCode": "PUR",
        "account": {
            "accountCode": "21C00005",
            "accountName": "Cyberview Technologies",
            "accountType": "S",
            "openingDate": "1975-05-04T18:30:00.000Z",
            "taxRegNumber": "1234567",
            "email": "paramdxb@yahoo.com",
            "contactNumber": "7339101188",
            "accountGroup": "2",
            "accountSubGroup": "21",
            "building": "Coimbatore",
            "street": "Radhakrishnan Salai",
            "locality": "Gandhipuram",
            "city": "Coimbatore",
            "state": "Tamil Nadu",
            "postalCode": "6100001",
            "_id": "664f7b1a7aba2a29b2e2d268",
            "createdAt": "2024-05-23T17:21:30.369Z",
            "updatedAt": "2024-05-23T17:21:30.369Z",
            "__v": 0
        },
        "voucherDate": "2024-05-29T05:31:14.191Z",
        "referenceNo": "5645",
        "referenceDate": null,
        "itemCollection": [
            {
                "srNumber": 1,
                "vrNumber": "12",
                "vrType": "PUR",
                "itemCode": "77.34.565",
                "itemName": "Engine Screw",
                "itemQty": 10,
                "itemRate": 30,
                "totalValue": 300,
                "sign": 1,
                "waCost": 0,
                "tax": 54,
                "_id": "6656bdaddc2f78dc3f743a41",
                "createdAt": "2024-05-29T05:31:25.921Z",
                "updatedAt": "2024-05-29T05:31:25.921Z"
            }
        ],
        "totalValue": 300,
        "totalTax": 54,
        "totalWaCost": 0,
        "journals": [
            {
                "vrNumber": "12",
                "vrDate": "2024-05-29T05:31:14.191Z",
                "vrType": "PUR",
                "accountCode": "13I00002",
                "accountName": "Inventory Control A/c",
                "journalAmount": 300,
                "sign": 1,
                "isBill": false,
                "settingKey": "Inventory",
                "_id": "6656bdaddc2f78dc3f743a3c",
                "createdAt": "2024-05-29T05:31:25.921Z",
                "updatedAt": "2024-05-29T05:31:25.921Z"
            },
            {
                "vrNumber": "12",
                "vrDate": "2024-05-29T05:31:14.191Z",
                "vrType": "PUR",
                "accountCode": "17G00011",
                "accountName": "GST input Receivable",
                "journalAmount": 54,
                "sign": 1,
                "isBill": false,
                "settingKey": "Tax",
                "_id": "6656bdaddc2f78dc3f743a3d",
                "createdAt": "2024-05-29T05:31:25.921Z",
                "updatedAt": "2024-05-29T05:31:25.921Z"
            },
            {
                "vrNumber": "12",
                "vrDate": "2024-05-29T05:31:14.191Z",
                "vrType": "PUR",
                "accountCode": "21C00005",
                "accountName": "Cyberview Technologies",
                "journalAmount": 354,
                "sign": -1,
                "isBill": true,
                "settingKey": "Supplier",
                "_id": "6656bdaddc2f78dc3f743a3b",
                "createdAt": "2024-05-29T05:31:25.921Z",
                "updatedAt": "2024-05-29T05:31:25.921Z"
            }
        ],
        "createdAt": "2024-05-29T05:31:25.922Z",
        "updatedAt": "2024-05-29T05:31:25.922Z",
        "__v": 0
    }
]

修改后的聚合查询 #

[
  {$unwind: '$journals'},
  {$group: {
    _id : { accountCode : '$journals.accountCode', accountName :'$journals.accountName' },
    amount : { $sum : { $multiply :['$journals.journalAmount', '$journals.sign']}},
    count : {$sum : 1}
  }},
  {$addFields: {fields : '$_id'}},
  {$project: {
    _id: 0,
    count: 1,
    fields: 1,
    Debit: {$cond: [{$gt: ['$amount', 0]}, '$amount', 0]},
    Credit: {$cond: [{$lt: ['$amount', 0]}, {$abs: '$amount'}, 0]}
  }}
]

关键修改说明 #

在原查询的最后一个$project阶段，新增了Debit和Credit字段的逻辑判断：

• 使用$cond判断amount的正负：若amount>0，Debit取amount的值，否则取0；若amount<0，Credit取amount的绝对值，否则取0
• 未在$project中指定amount字段，因此该字段会被自动排除，只保留需要的count、fields、Debit和Credit字段

内容的提问来源于stack exchange，提问作者Manickam Parameswaran