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
}
]
2
Answers
this is the query given me the result.
You can follow the query,
$addFields
stage before$group
stage,amount
field,Playground