MongoDB nested array query how to-CodePudding

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!

CodePudding user response：

If you want to filter dates between dateOfStart and dateOfTermination you should invert the $gte and $lte conditions:

{
  "$and": [
    { "subEntities.dateOfStart": { "$gte": "2098-01-02" } },
    { "subEntities.dateOfTermination": { "$lte": "2099-12-30" } },

    { "subEntities.subId": { "$in": ["998"] } }
  ]
}

CodePudding user response：

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:

db.collection.find({
  subEntities: {
    $elemMatch: {
      dateOfStart: {
        "$lte": "2098-01-02"
      },
      dateOfTermination: {
        "$gte": "2099-12-30"
      },
      subId: {
        "$in": [
          "998"
        ]
      }
    }
  }
})

Mongo Playground