MongoDB Query on multiple field condition-CodePudding

I have a MongoDB model that is currently like this (this is the stripped version):

{
  title: String,
  type: {
    type: String,
    lowercase: true,
    enum: ['event', 'regular', 'project'],
  },
  project_start_time: Date,
  project_end_time: Date,
  regular_start_date: Date,
  regular_end_date: Date,
  events: [{
    id: Number,
    date: Date
  }]
}

Now, I want to query something like this:

Find data where the regular_end_date, project_end_time, and events at the last index are lower than the date provided

The catch is, not every data has the three criteria above because it is available according to the types (Sorry for the messy data, it is already there). Below is an example:

If the data type is an event, then there are events
If the data type is regular, then there are regular_start_date and regular_end_date
If the data type is a project, then there are project_start_date and project_end_date

So far, I've tried to use this:

db.data.find({
  "$or": [
    {
      "project_end_time": {
        "$lt": ISODate("2022-12-27T10:09:49.753Z")
      },
    },
    {
      "regular_end_date": {
        "$lt": ISODate("2022-12-27T10:09:49.753Z")
      }
    },
    {
      "$expr": {
        "$lt": [
          {
            "$getField": {
              "field": "date",
              "input": {
                "$last": "$events"
              }
            }
          },
          ISODate("2022-12-27T10:09:49.753Z")
        ]
      }
    }
  ]
})

Also with aggregation pipeline:

db.data.aggregate([
  {
    $match: {
      "$or": [{
        "project_end_time": {
          "$lt": ISODate("2022-12-27T10:09:49.753Z")
        },
      },
      {
        "regular_end_date": {
          "$lt": ISODate("2022-12-27T10:09:49.753Z")
        }
      },
      {
        "$expr": {
          "$lt": [{
            "$getField": {
              "field": "date",
              "input": {
                "$last": "$events"
              }
            }
          },
          ISODate("2022-12-27T10:09:49.753Z")
        ]}
      }]
    }
  }
])

But it shows all data as if it wasn't filtered according to the criteria. Any idea where did I do wrong?

FYI I am using MongoDB 5.0.2

CodePudding user response：

One option is to check if the relevant field exists before checking its value, otherwise its value is null which is less than your requested date:

db.collection.find({
  $or: [
    {$and: [
        {project_end_time: {$exists: true}},
        {project_end_time: {$lt: ISODate("2022-12-27T10:09:49.753Z")}}
    ]},
    {$and: [
        {regular_end_date: {$exists: true}},
        {regular_end_date: {$lt: ISODate("2022-12-27T10:09:49.753Z")}}
    ]},
    {$and: [
        {"events.0": {$exists: true}},
        {$expr: {
            $lt: [
              {$last: "$events.date"},
              ISODate("2022-12-27T10:09:49.753Z")
            ]
        }}
    ]}
  ]
})

See how it works on the playground example