How to Dynamically Compare Fields Across Documents in an Aggregated Array in MongoDB

Working with Data
, , ,
1

I have a MongoDB aggregation result where multiple documents are aggregated into an array. I need to compare fields across documents within the same array using dynamic conditions, which could involve arithmetic or logical operations.

[
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “673dd3f0db967400132382d0”,
“fileid_671b60d34283d243e75f107e”: 1,
“approvalvalueoffile_671b60d34283d243e75f107e”: 500000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “11-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Pending”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “673ddd50db967400132382ff”,
“fileid_671b60d34283d243e75f107e”: 17,
“approvalvalueoffile_671b60d34283d243e75f107e”: 300000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “12-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Pending”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “67456c50db9674001323832e”,
“fileid_671b60d34283d243e75f107e”: 33,
“approvalvalueoffile_671b60d34283d243e75f107e”: 100000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “13-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Pending”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “67456c50db9674001323832e”,
“fileid_671b60d34283d243e75f107e”: 33,
“approvalvalueoffile_671b60d34283d243e75f107e”: 100000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “13-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Pending”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “67498b3cdb9674001323837d”,
“fileid_671b60d34283d243e75f107e”: 33,
“approvalvalueoffile_671b60d34283d243e75f107e”: 100000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “13-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Pending”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “67498b3cdb9674001323837d”,
“fileid_671b60d34283d243e75f107e”: 49,
“approvalvalueoffile_671b60d34283d243e75f107e”: 100000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “14-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Pending”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “674ed13cdb967400132383cc”,
“fileid_671b60d34283d243e75f107e”: 49,
“approvalvalueoffile_671b60d34283d243e75f107e”: 100000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “14-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Pending”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “6752e7b8db96740013238403”,
“fileid_671b60d34283d243e75f107e”: 1,
“approvalvalueoffile_671b60d34283d243e75f107e”: 500000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “11-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Approved”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “67572be8db96740013238432”,
“fileid_671b60d34283d243e75f107e”: 17,
“approvalvalueoffile_671b60d34283d243e75f107e”: 300000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “12-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Pending”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “67572be8db96740013238432”,
“fileid_671b60d34283d243e75f107e”: 17,
“approvalvalueoffile_671b60d34283d243e75f107e”: 300000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “12-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Pending”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “67573020db96740013238481”,
“fileid_671b60d34283d243e75f107e”: 17,
“approvalvalueoffile_671b60d34283d243e75f107e”: 300000,
“filesentforapprovaldate_671b60d34283d243e75f107e”: “12-11-2024”,
“approvalstatus_671b60d34283d243e75f107e”: “Pending”
},
{
“employeeid”: “orgs1.919705394882”,
“transaction_id”: “674ed13cdb967400132383cc”,
“fileid_673ae6a14283d243e775eec0”: 1,
“dispatchstatus_673ae6a14283d243e775eec0”: “Pending”,
“dateofdispatch_673ae6a14283d243e775eec0”: “NA”,
“valueofdispatchfile_673ae6a14283d243e775eec0”: 500000
}
]

2

We cannot work with your sample documents as published.

Please read Formatting code and log snippets in posts and update accordingly.

But a better alternative is to create a

You would also need to provide a lot more details and examples of