Mongodb Question posted in
The official documentation can be found here.

Add new field based on the value of the existing field – MongoDB Aggregate

This is my MongoDB aggregate result based on the below aggregate query.

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

// the query is :

  [

  {$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}}          
            
  ]

// the result I am expecting is

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

in the result, instead of the field amount, I want ‘Credit’ if the amount is negative and ‘Debit’ if the amount is positive. Please help me to rewrite this query.

Actual Document is like the one below

[
    {
        "_id": "6656bdaddc2f78dc3f743a3f",
        "voucherNumber": "12",
        "voucherCode": "PUR",
        "account": {
            "accountCode": "21C00005",
            "accountName": "Cyberview Technologies",
            "accountType": "S",
            "openingDate": "1975-05-04T18:30:00.000Z",
            "taxRegNumber": "1234567",
            "email": "[email protected]",
            "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
    }
]
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    this is the query given me the result.

    [
  {$unwind: '$journals'},
  {
    $group: {
      _id: {
        accountCode: "$journals.accountCode",
        accountName: "$journals.accountName"
      },
      Debit: {
        $sum: {
          $cond: [
            { $eq: ["$journals.sign", 1] },
            "$journals.journalAmount",
            0
          ]
        }
      },
      Credit: {
        $sum: {
          $cond: [
            { $eq: ["$journals.sign", -1] },
            "$journals.journalAmount",
            0
          ]
        }
      },
      count: {
        $sum: 1
      }
    }
  }
 ]

  2. 0

    You can follow the query,

    • Do that multiplication operation in a $addFields stage before $group stage,
      {
    $addFields: {
      amount: {
        $multiply: [
          "$journals.journalAmount",
          "$journals.sign"
        ]
      }
    }
  }
    • Calculate the Debit and Credit on the basis of amount field,
      {
    $group: {
      _id: {
        accountCode: "$journals.accountCode",
        accountName: "$journals.accountName"
      },
      Debit: {
        $sum: {
          $cond: [
            { $lt: ["$amount", 0] },
            "$amount",
            0
          ]
        }
      },
      Credit: {
        $sum: {
          $cond: [
            { $gt: ["$amount", 0] },
            "$amount",
            0
          ]
        }
      },
      count: {
        $sum: 1
      }
    }
  }

    Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search