Self-join in MongoDb

Working with Data
1

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

3

My approach would be a little different.

  1. In the $group stage, I would not

  1. I will keep the $match as it it

  2. I don’t think you really need to $sort on count:1

  3. I would $lookup into audit $match-ing xCorrelationId (_id after $group) and modifiedDate (which is the $max in the $group) to find modifiedBy.