I have a similar collection where I have sort them by their startTime:

{"name": 'A', "startTime": '1634626355', "endTime": '1634631405'}
{"name": 'A', "startTime": '1634631406', "endTime": '1634631864'}
{"name": 'A', "startTime": '1634631865', "endTime": '1634656048'}
{"name": 'A', "startTime": '1634712642', "endTime": '1634718856'}

How can I compare the documents such that if the document endTime and the next document startTime duration is less than 5 minutes, merge it.

This is the result I'm trying to achieve (The 1st 3 documents are merged into 1 where it uses the startTime of the 1st document and the endTime of the 3rd document):

{"name": 'A', "startTime": '1634626355', "endTime": '1634656048'}
{"name": 'A', "startTime": '1634712642', "endTime": '1634718856'}

Thanks

CodePudding user response：

First of all, you should never store date/time values as string, it's a design flaw. Store always proper Date object.

This solution works without self-lookup, so it may perform better:

db.collection.aggregate([
   {
      $set: {
         startDateTime: { $toDate: { $multiply: ["$startTime", 1000] } },
         endDateTime: { $toDate: { $multiply: ["$endTime", 1000] } }
      },
   },
   { $sort: { startDateTime: 1 } },
   { $group: { _id: null, data: { $push: "$$ROOT" } } },
   {
      $set: {
         data: {
            $reduce: {
               input: "$data",
               initialValue: [],
               in: {
                  $cond: {
                     if: {
                        $or: [
                           { $eq: [{ $size: "$$value" }, 0] }, // for the initail element
                           {
                              $gt: [
                                 {
                                    $dateDiff: { // calculate difference
                                       endDate: "$$this.startDateTime",
                                       startDate: { $last: "$$value.endDateTime" },
                                       unit: "minute"
                                    }
                                 },
                                 5 // more than 5 Minutes
                              ]
                           }
                        ]
                     },
                     then: { $concatArrays: ["$$value", ["$$this"]] }, // append new element
                     else: {
                        $map: {
                           input: "$$value",
                           as: "data",
                           in: {
                              $cond: {
                                 if: { $eq: ["$$data._id", { $last: "$$value._id" }] }, // find last element
                                 then: { // update last element
                                    $mergeObjects: [
                                       "$$data",
                                       { endDateTime: "$$this.endDateTime" },
                                       { endTime: "$$this.endTime" }
                                    ]
                                 }, 
                                 else: "$$data"
                              }
                           }
                        }
                     }
                  }
               }
            }
         }
      }
   },
   // some cosmetic
   { $unwind: "$data" },
   { $replaceRoot: { newRoot: "$data" } }
])

Mongo Playground

CodePudding user response：

You can use $lookup in an aggregation pipeline to find out the documents that you need to remove. Then, perform a forEach to remove them.

db.collection.aggregate([
  {
    $addFields: {
      endDateTime: {
        "$toDate": {
          "$multiply": [
            {
              $toLong: "$endTime"
            },
            1000
          ]
        }
      }
    },
    
  },
  {
    "$lookup": {
      "from": "collection",
      let: {
        end: "$endDateTime"
      },
      pipeline: [
        {
          "$addFields": {
            startDateTime: {
              "$toDate": {
                "$multiply": [
                  {
                    $toLong: "$startTime"
                  },
                  1000
                ]
              }
            }
          }
        },
        {
          $match: {
            $expr: {
              $and: [
                {
                  $lte: [
                    {
                      $subtract: [
                        "$startDateTime",
                        "$$end"
                      ]
                    },
                    300000
                  ]
                },
                {
                  $lte: [
                    "$$end",
                    "$startDateTime"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "lessThan5min"
    }
  },
  {
    "$unwind": "$lessThan5min"
  },
  {
    "$replaceRoot": {
      "newRoot": "$lessThan5min"
    }
  }
]).forEach(function(doc){
  db.collection.remove({ "_id": doc._id });
});

Here is the Mongo playground to find out the documents that you need to remove for your reference.