Query MongoDB nested array from the same nest

Question

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?

nimrod serok · Accepted Answer · 2022-07-28 13:34:50Z

1

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

answered Jul 28, 2022 at 13:34

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query MongoDB nested array from the same nest

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related