I am trying to find duplicated data based on xCorrelationID in a collection as mentioned below:
{
"_id" : "627jhjhghgf5251c87klkjkj601aee",
"creationDate" : "2022-05-12T17:16:31.788+0000",
"modifiedDate" : "2022-05-12T17:16:43.062+0000",
"claimNumber" : "Q030NEE01932",
"xCorrelationId" : "b25a19ad-7816-4288-a0d9-becc0ac0a5db",
"modifiedBy" : "12311684",
"events" : [{
"abc":1
}
],
"_class" : "com.xyaxdd"
},
{
"_id" : "627d40f05251c87c3f601aee",
"creationDate" : "2022-09-12T17:16:31.788+0000",
"modifiedDate" : "2022-09-12T17:16:43.062+0000",
"claimNumber" : "Q030NEE0jhjhj",
"xCorrelationId" : "b25a19ad-7816-4288-a0d9-becc0ac0a5db",
"modifiedBy" : "456311684",
"events" : [{
"xyz":3455
}
],
"_class" : "com.AuditRecord"
}
I have the following query which works fine but The piece where I am stuck is on modifiedBy field. The value of modifiedBy should correspond for the document with max(modifiedDate) within a group of related xCorrelationID. For example, if there are 10 documents within a group of related xCorrelationId and 5th document has max date for modifiedDate then I need to take modifiedBy value for that 5th document
db.audit.aggregate([
{$group : { "_id": "$xCorrelationId", "count": { $sum: 1 },
events: {$push: "$events"},
creationDate: { $min: "$creationDate" }, modifiedDate: { $max: "$modifiedDate" },claimNumber:{$first: "$claimNumber"}, _class:{$first: "$_class"}
},
},
{$match: {"_id" :{ $ne : null } , "count" : {$gt: 1} } },
{$sort: {count: -1}},
{$project: {"xCorrelationId" : "$_id", "_id" : 0, count: 1,
events:{
$reduce: {
input: '$events',
initialValue: [],
in: {$concatArrays: ['$value', '$this']}
}
},
creationDate: 1, modifiedDate: 1, claimNumber: 1, _class: 1
} },
],
{allowDiskUse:true});
modifiedBy can be achieved in SQL DB with following query.
SELECT modifiedBy
FROM dpr_audit_record s
JOIN (SELECT MAX(modifiedDate) AS md, _id FROM dpr_audit_record GROUP BY xCorrelationId) max
ON s._id = max._id
Can anybody help me to transform it in mongodb? I am trying to use $lookup after $group in above query but failing.
PS: Mongodb version - 4.4.15