find overlapping dates within mongoDB array objects-CodePudding

I have a MongoDB document collection with multiple arrays that looks like this :

{
"_id": "1235847",
  "LineItems": [
    {
      "StartDate": ISODate("2017-07-31T00:00:00.000 00:00"),
      "EndDate": ISODate("2017-09-19T00:00:00.000 00:00"),
      "Amount": {"$numberDecimal": "0.00"}
    },
    {
      "StartDate": ISODate("2022-03-20T00:00:00.000 00:00"),
      "EndDate": ISODate("2022-10-21T00:00:00.000 00:00"),
      "Amount": {"$numberDecimal": "6.38"}
    },
    {
      "StartDate": ISODate("2022-09-20T00:00:00.000 00:00"),
      "EndDate": ISODate("9999-12-31T00:00:00.000 00:00"),
      "Amount": {"$numberDecimal": "6.17"}
    }
  ]
}

Is there a simple way to find documents where the startdate has overlapped with previously startdate, enddate? The startdate can not be before previous end dates within the array The start/end can not be between previous start/end dates within the array

The below works but I don't want to hardcode the array index to find all the documents

{
    $match: {
        $expr: {
            $gt: [
                'LineItems.3.EndDate',
                'LineItems.2.StartDate'
            ]
        }
    }
}

CodePudding user response：

Here's one way you could find docs where "StartDate" is earlier than the immediately previous "EndDate".

db.collection.find({
  "$expr": {
    "$getField": {
      "field": "overlapped",
      "input": {
        "$reduce": {
          "input": {"$slice": ["$LineItems", 1, {"$size": "$LineItems"}]},
          "initialValue": {
            "overlapped": false,
            "prevEnd": {"$first": "$LineItems.EndDate"}
          },
          "in": {
            "overlapped": {
              "$or": [
                "$$value.overlapped",
                {"$lt": ["$$this.StartDate", "$$value.prevEnd"]}
              ]
            },
            "prevEnd": "$$this.EndDate"
          }
        }
      }
    }
  }
})

Try it on mongoplayground.net.