Question 113540 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – MongoDB nested array query how to

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!

Tags:

2

Answers


  1. 0

    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"] } }
  ]
}
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search