How to find duplicate records based on an id and a datetime field in MongoDB?

user_15890987 0
Viewed 65

I have a MongoDB collection with millions of record. Sample records are shown below:

[
  {
    _id: ObjectId("609977b0e8e1c615cb551bf5"),
    activityId: "123456789",
    updateDateTime: "2021-03-24T20:12:02Z"
  },
  {
    _id: ObjectId("739177b0e8e1c615cb551bf5"),
    activityId: "123456789",
    updateDateTime: "2021-03-24T20:15:02Z"
  },
  {
    _id: ObjectId("805577b0e8e1c615cb551bf5"),
    activityId: "123456789",
    updateDateTime: "2021-03-24T20:18:02Z"
  }
]

Multiple records could have the same activityId, in this case i want just the record that has the largest updateDateTime.

I have tried doing this and it works fine on a smaller collection but times out on a large collection.

[
  {
    $lookup: {
      from: "MY_TABLE",
      let: {
        existing_date: "$updateDateTime",
        existing_sensorActivityId: "$activityId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$activityId", "$$existing_sensorActivityId"] },
                { $gt: ["$updateDateTime", "$$existing_date"] }
              ]
            }
          }
        }
      ],
      as: "matched_records"
    }
  },
  { $match: { "matched_records.0": { $exists: true } } },
  { $project: { _id: 1 } }
]

This gives me _ids for all the records which have the same activity id but smaller updateDateTime.

The slowness occurs at this step -> "matched_records.0": {$exists:true}

Is there a way to speed up this step or are there any other approach to this problem?

aggregation-framework mongodb nosql group-by
1 Answers

You can find unique documents and write result in new collection using $out instead of finding duplicate documents and deleting them,

How to find unique documents?

  • $sort by updateDateTime in descending order
  • $group by activityId and get first root record
  • $replaceRoot to replace record in root
  • $out to write query result in new collection
[
  { $sort: { updateDateTime: -1 } },
  {
    $group: {
      _id: "$activityId",
      record: { $first: "$$ROOT" }
    }
  },
  { $replaceRoot: { newRoot: "$record" } },
  { $out: "newCollectionName" } // set new collection name
]

Playground

user_89871280
Related