Hi @emmanuel_bernard - Thanks for providing those details.
Use of the aggregation pipeline was something I was going to suggest. I used $filter to identify all the mismatches. The example aggregation provided only identifies mismatches and doesn’t modify them. Correcting them would require an extra / seperate step.
An aggregation pipeline with $match and $reduce (or $map or $filter) is a solution, but I’m wondering if $elemMatch applied on saleRows could do the job ?
I’m wondering is there a particular reason you’re after using a .find() with $expr and $elemMatch over the aggregation solution you mentioned? I have not tried this method myself yet but it may not be possible due to the sub-documents within the saleRows array.
Example aggregation:
/// variable y set to the following:
DB> y
{
'$filter': {
input: '$saleRows',
as: 'saleRow',
cond: {
'$ne': [
{$round:[{ '$toDouble': '$$saleRow.currentAmountIncludingTax' },2]},
{$round:[{
'$multiply': [
{ '$toDouble': '$$saleRow.quantity' },
{ '$toDouble': '$$saleRow.currentPriceIncludingTax' }
]
},2]}
]
}
}
}
/// Aggregation using `filter` from above, projecting the mismatches. Used `$round` to try and avoid binary rounding error
DB> db.Sales.aggregate({$addFields:{mismatches:y}},{$project:{mismatches:1}})
[
{ _id: ObjectId("62e1be991bb9515b8fbd4fe7"), mismatches: [] },
{
_id: ObjectId("62e1be991bb9515b8fbd4fe8"),
mismatches: [
{
rowId: '2',
quantity: 1,
currentPriceIncludingTax: 17.95,
currentAmountIncludingTax: 29.95
}
]
},
{ _id: ObjectId("62e1be991bb9515b8fbd4fe9"), mismatches: [] },
{
_id: ObjectId("62e1be991bb9515b8fbd4fea"),
mismatches: [
{
rowId: '1',
quantity: 1,
currentPriceIncludingTax: 20.95,
currentAmountIncludingTax: 24.95
}
]
},
{ _id: ObjectId("62e1dd7d1bb9515b8fbd4feb"), mismatches: [] }
]
Note: I added another document to my test environment which was not supposed to be returned as it is equal after the multiplication but due to binary rounding details, I had added the $round to try and avoid this.
Additional document which was added:
{
_id: ObjectId("62e1dd7d1bb9515b8fbd4feb"),
saleRows: [
{
rowId: '1',
quantity: 1,
currentPriceIncludingTax: 24.95,
currentAmountIncludingTax: 24.95
},
{
rowId: '2',
quantity: 3,
currentPriceIncludingTax: 17.95,
currentAmountIncludingTax: 53.85
}
]
}
Please note this may not be the exact desired output you want here but the above would show the mismatches and you can alter the pipeline to better suit your use case if required.
As with any of these operations / suggestions, please thoroughly test this in a test environment to verify it suits all your use cases and requirements.
In saying so, it would possibly be faster (and more straightforward) to fetch all the documents and then proceed to perform the calculations on the client side. Maybe for simplicity with expressing the operation required / getting the desired output whilst also doing the calculation and query in a singular operation, then aggregation might be better suited.
Regards,
Jason
