I am trying to query a document in my MongoDB
Document:
{
_id: '111',
subEntities: [
{
subId: '999',
dateOfStart: '2098-01-01',
dateOfTermination: '2099-12-31'
},
{
subId: '998',
dateOfStart: '2088-01-01',
dateOfTermination: '2089-12-31'
}
]
}
My Query:
{"$and": [
{"subEntities.dateOfStart": {"$lte": "2098-01-02"}},
{"subEntities.dateOfTermination": {"$gte": "2099-12-30"}},
{"subEntities.subId": {"$in": ["998"]}}
]}
As you can see, I am trying to apply a date value and an ID to the subentities.
The date value should be between dateOfStart and dateOfTermination.
The query returns a match, although the date value only matches the first subentity and the ID query matches the second subquery.
How can I make it so that there is only one match when both queries match the same subentity?
Can I aggregate the subentities?
Thanks a lot!
2
Answers
If you want to filter dates between
dateOfStart
anddateOfTermination
you should invert the$gte
and$lte
conditions:When you query arrays Mongo by default "flattens" them, which means each condition of the query get’s executed independently.
You want to be using $elemMatch, this allows you to query full objects from within an array, like so:
Mongo Playground