Query MongoDB nested array from the same nest-CodePudding

I have a collection whose typical documents look like this:

doc = {
    "_id": "id1",
    "properties": [
        {
            "type": "value1",
            "start_date": "2022-03-03T00:00:00.000 00:00",
            "end_date": "2022-05-03T00:00:00.000 00:00"
        },
        {
            "type": "value2",
            "start_date": "2022-05-15T00:00:00.000 00:00",
            "end_date": "2022-06-08T00:00:00.000 00:00"
        },
        {
            "type": "value3",
            "start_date": "2022-06-21T00:00:00.000 00:00",
            "end_date": "2022-07-04T00:00:00.000 00:00"
        }
    ]
}

I would like to make a query to identify which value was active at a specific date. At the moment, I have:

{"properties.type": value2, "properties.start_date": {$lt: ISODate('2022-06-01')}, "properties.end_date": {$gt: ISODate('2022-06-01')}}

However, I have the impression that this request also return other documents whose dates fits with another value, such as:

doc = {
    "_id": "id2",
    "properties": [
        {
            "type": "value2",
            "start_date": "2022-03-03T00:00:00.000 00:00",
            "end_date": "2022-05-03T00:00:00.000 00:00"
        },
        {
            "type": "value5",
            "start_date": "2022-05-15T00:00:00.000 00:00",
            "end_date": "2022-06-08T00:00:00.000 00:00"
        },
        {
            "type": "value1",
            "start_date": "2022-06-21T00:00:00.000 00:00",
            "end_date": "2022-07-04T00:00:00.000 00:00"
        }
    ]
}

Would it be possible to change the query in order to force the results to be within the same nest of the array, and therefore only return document id1?

CodePudding user response：

One option is to use $elemMatch to find documents where the is at least one item in properties that matches all the conditions:

db.collection.find({
  properties: {
    $elemMatch: {
      type: "value2",
      start_date: {$lt: ISODate("2022-06-01")},
      end_date: {$gt: ISODate("2022-06-01")}
    }
  }
})

See how it works on the playground example