Query between two dates returns unexpected documents

Working with Data
,
1

How would I write a query which checks the Children.DateOfBirth and finds all documents between 01-01-2017 and 01-01-2018?
I am storing the DOB as a string, transforming them in the pipeline, then applying the filtering for the DOB. However I am getting back documents that are outside the given date range.

Here is how my documents look like:
1

This is the aggregation pipeline that I am using:

[{$set: {
Children: {
  $map: {
   input: '$Children',
   'in': {
    DateOfBirthDateTime: {
     $cond: [
      {
       $ne: [
        '$$this.DateOfBirth',
        ''
       ]
      },
      {
       $toDate: '$$this.DateOfBirth'
      },
      null
     ]
    },
    Name: '$$this.Name'
   }
  }
}
}}, {$match: {
'Children.DateOfBirthDateTime': {
  $gte: ISODate('2017-01-01'),
  $lte: ISODate('2018-01-01')
}
}}]
2

Hi @Laura_Mazarini and welcome in the MongoDB Community :muscle: !

I think your pipeline works as intended but I suspect that you are getting results that you don’t expect because you are working on an array and not a single value.

With your current pipeline if ONE child within the array is in the range, you get the entire doc (with the entire array of children in it).

I illustrated this here in Compass:

image
image1342×1010 121 KB

The doc with a single child born in 2008 is discarded while the one with 2 children born in 2008 and 2010 is selected.

If you only want the 2nd child, you must $unwind the array first to break the array.

Cheers,
Maxime.

3

Hi Maxime! Thanks for the answer, however is still not getting me the wanted result.

In your example the date range filter that you are using is “2009-01-01” to “2010-01-01”. However you are getting back a document in which none of the dates from the Children array is between the given ones, since the DOB for child1 is “2008-05-05” and the DOB for child2 is “2010-05-05”. The second child matches the year, but it is out of the date range with more than 4 months. This is exactly my issue as well.

If any of the Children’s DOB form the array falls within the date range I want the entire document, so I don’t think the unwind is wanted for my scenario. I need to search the entire array, but like in your example I get back some false positives.

4

Oops my bad :joy: ! I went a little too fast on this one !

You need a $elemMatch to make sure that your filter applies to the same array element.

I tested again with a few examples to prove it’s working fine this time. :+1:

test [direct: primary] test> db.coll.drop()
true
test [direct: primary] test> db.coll.insertMany([{Children: [{DateOfBirth: "2010-05-05"}, {DateOfBirth: "2012-06-06"}]},{Children: [{DateOfBirth: "2008-02-02"}]}])
{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId("638a6ffcbd6d1ee1c17b6ce2"),
    '1': ObjectId("638a6ffcbd6d1ee1c17b6ce3")
  }
}
test [direct: primary] test> db.coll.find()
[
  {
    _id: ObjectId("638a6ffcbd6d1ee1c17b6ce2"),
    Children: [ { DateOfBirth: '2010-05-05' }, { DateOfBirth: '2012-06-06' } ]
  },
  {
    _id: ObjectId("638a6ffcbd6d1ee1c17b6ce3"),
    Children: [ { DateOfBirth: '2008-02-02' } ]
  }
]
test [direct: primary] test> db.coll.aggregate([
...   {
...     '$set': {
...       'Children': {
...         '$map': {
...           'input': '$Children', 
...           'in': {
...             'DateOfBirthDateTime': {
...               '$cond': [
...                 {
...                   '$ne': [
...                     '$$this.DateOfBirth', ''
...                   ]
...                 }, {
...                   '$toDate': '$$this.DateOfBirth'
...                 }, null
...               ]
...             }, 
...             'Name': '$$this.Name'
...           }
...         }
...       }
...     }
...   }, {
...     '$match': {
...       'Children': {
...         '$elemMatch': {
...           'DateOfBirthDateTime': {
...             '$gte': new Date('Sun, 01 Jan 2017 00:00:00 GMT'), 
...             '$lte': new Date('Wed, 01 Jan 2020 00:00:00 GMT')
...           }
...         }
...       }
...     }
...   }
... ])

test [direct: primary] test> db.coll.aggregate([ { '$set': { 'Children': { '$map': { 'input': '$Children', 'in': { 'DateOfBirthDateTime': { '$cond': [ { '$ne': [ '$$this.DateOfBirth', ''] }, { '$toDate': '$$this.DateOfBirth' }, null] }, 'Name': '$$this.Name' } } } } }, { '$match': { 'Children': { '$elemMatch': { 'DateOfBirthDateTime': { '$gte': new Date('Sun, 01 Jan 2007 00:00:00 GMT'), '$lte': new Date('Wed, 01 Jan 2009 00:00:00 GMT') } } } } }])
[
  {
    _id: ObjectId("638a6ffcbd6d1ee1c17b6ce3"),
    Children: [ { DateOfBirthDateTime: ISODate("2008-02-02T00:00:00.000Z") } ]
  }
]
test [direct: primary] test> db.coll.aggregate([ { '$set': { 'Children': { '$map': { 'input': '$Children', 'in': { 'DateOfBirthDateTime': { '$cond': [ { '$ne': [ '$$this.DateOfBirth', ''] }, { '$toDate': '$$this.DateOfBirth' }, null] }, 'Name': '$$this.Name' } } } } }, { '$match': { 'Children': { '$elemMatch': { 'DateOfBirthDateTime': { '$gte': new Date('Sun, 01 Jan 2009 00:00:00 GMT'), '$lte': new Date('Wed, 01 Jan 2011 00:00:00 GMT') } } } } }])
[
  {
    _id: ObjectId("638a6ffcbd6d1ee1c17b6ce2"),
    Children: [
      { DateOfBirthDateTime: ISODate("2010-05-05T00:00:00.000Z") },
      { DateOfBirthDateTime: ISODate("2012-06-06T00:00:00.000Z") }
    ]
  }
]
test [direct: primary] test> db.coll.aggregate([ { '$set': { 'Children': { '$map': { 'input': '$Children', 'in': { 'DateOfBirthDateTime': { '$cond': [ { '$ne': [ '$$this.DateOfBirth', ''] }, { '$toDate': '$$this.DateOfBirth' }, null] }, 'Name': '$$this.Name' } } } } }, { '$match': { 'Children': { '$elemMatch': { 'DateOfBirthDateTime': { '$gte': new Date('Sun, 01 Jan 2007 00:00:00 GMT'), '$lte': new Date('Wed, 01 Jan 2011 00:00:00 GMT') } } } } }])
[
  {
    _id: ObjectId("638a6ffcbd6d1ee1c17b6ce2"),
    Children: [
      { DateOfBirthDateTime: ISODate("2010-05-05T00:00:00.000Z") },
      { DateOfBirthDateTime: ISODate("2012-06-06T00:00:00.000Z") }
    ]
  },
  {
    _id: ObjectId("638a6ffcbd6d1ee1c17b6ce3"),
    Children: [ { DateOfBirthDateTime: ISODate("2008-02-02T00:00:00.000Z") } ]
  }
]

Cheers,
Maxime.

1 Like