MongoDB $and operator with nested objects in arrays not working as expected

Question

I have nested array database records styled as such:

{
"_id" : "A",
"foo" : [ 
    {
        "_id" : "a",
        "date" : ISODate("2017-07-13T23:27:13.522Z")
    }, 
    {
        "_id" : "b",
        "date" : ISODate("2017-08-04T22:36:36.381Z")
    }, 
    {
        "_id" : "c",
        "date" : ISODate("2017-08-23T23:59:40.202Z")
    }
]
},
{
"_id" : "B",
"foo" : [ 
    {
        "_id" : "d",
        "date" : ISODate("2017-07-17T23:27:13.522Z")
    }, 
    {
        "_id" : "e",
        "date" : ISODate("2017-01-06T22:36:36.381Z")
    }, 
    {
        "_id" : "f",
        "date" : ISODate("2017-09-14T23:59:40.202Z")
    }
]
},
{
"_id" : "C",
"foo" : [ 
    {
        "_id" : "g",
        "date" : ISODate("2017-11-17T23:27:13.522Z")
    }, 
    {
        "_id" : "h",
        "date" : ISODate("2017-06-06T22:36:36.381Z")
    }, 
    {
        "_id" : "i",
        "date" : ISODate("2017-10-14T23:59:40.202Z")
    }
]
}

When I run the query:

db.bar.find(
    {
        $and: [
                  {"foo.date": {$lte: new Date(2017,8,1)}}, 
                  {"foo.date": {$gte: new Date(2017,7,1)}}
       ]
    },
    {
       "_id":1
    }
)

I'm returned

{ 
    _id: "A"
},
{ 
    _id: "B"
},
{ 
    _id: "C"
}

Logically I'm asking for only the records where at least one date is between Aug-1 and Sept-1 (Record A), but am getting all records.

I'm thinking it might be referencing different dates on the subdocuments i.e. where foo.1.date > Aug-1 and foo.0.date < Sept-1.

Has anyone else had issue and found a resolution to this?

mickl · Accepted Answer · 2017-11-27 21:08:52Z

1

Your filters are evaluated separately against each subdocument in your array and that's why you're getting all results. For instance for C

element with _id g is gte 1st of August
element with _id h is lte 1st of September

You should use $elemMatch to find date in specified range

    db.bar.find(
    { "foo": 
        { 
            "$elemMatch": 
                { "date": 
                    { 
                        "$gte": new Date(2017,7,1), 
                        "$lte": new Date(2017,8,1) 
                    } 
                } 
            } 
    })

Only A will be returned for this query.

answered Nov 27, 2017 at 21:08

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

1 Comment

Robert Ricketts Over a year ago

Thanks, I was suspecting that was the issue. $elemMatch was the solution.

andre luis borges de paula · Accepted Answer · 2017-11-27 21:02:46Z

0

The way you are doing doensn't work as you want for array.

You have to unpack the array and after you compare the values.

 db.bar.aggregate(
// Unpack the assignments array
{ $unwind : "$foo" },

// Find the assignments ending after given date
{ $match : {
    "foo.date": { $gte: new Date(2017,7,1),$lt: new Date(2017,8,1)  }
}}
)

answered Nov 27, 2017 at 21:02

andre luis borges de paula

1,1272 gold badges8 silver badges10 bronze badges

1 Comment

Robert Ricketts Over a year ago

Thanks, this was a valid response, but for my use case required using "find".

Shubham · Accepted Answer · 2017-11-30 09:18:25Z

0

This should also work fine

 db.bar.find({"foo.date": { $gte: new Date(2017,7,1),$lt: new Date(2017,8,1)  }})

answered Nov 30, 2017 at 9:18

Shubham

1,44610 silver badges18 bronze badges

1 Comment

Robert Ricketts Over a year ago

yes, that's equivalent to the query I was running in my initial post. Make a collection with the sample data I provided and you'll see that it returns [A, B, C]

Collectives™ on Stack Overflow

MongoDB $and operator with nested objects in arrays not working as expected

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related