MongoDB: Add date comparison to arrayFilters

user_6898421 0
Viewed 39

Objects of my collection have a field, that is an array of objects with one of the field being a string date

{
    citizens: [{
      name: 'John'
      birthday: '1993/07/13'
    }, 
    {
      name: 'Sarah'
      birthday: '1996/07/13'
    },
    {
      name: 'Natalia',
      birthday: '2015/07/13'
    }]
}
{
    citizens: [{
      name: 'Leo'
      birthday: '1994/02/08'
    }, 
    {
      name: 'Paul'
      birthday: '1934/09/13'
    },
    {
      name: 'Rego',
      birthday: '2019/01/29'
    }]
}

I want to set to all the users older than 18 status 'adult' Here is what I try to do:

  users.updateMany({}, {
    $set: { 'citizens.$[elem].status': 'adult' },
  },
  {
    arrayFilters: [
      { 'elem.status': { $exists: false } },
      { $lt: [{ $toDate: 'elem.$birthday' }, 18yearsaAgoDate] }, <-- 18years don't mean much here, I actually use $$NOW
    ],
    multi: true,
  });

But I get 'unknown top level operator: $lt' error when run this. How do I supposed to use $lt in arrayFilter?

Thanks in advance!

mongodb-query mongodb
3 Answers

Here's how you could do it in a simple update using the aggregation pipelined updates:

db.collection.updateMany({},
[
  {
    $set: {
      citizens: {
        $map: {
          input: "$citizens",
          in: {
            $mergeObjects: [
              {
                status: {
                  $cond: [
                    {
                      $gt: [
                        {
                          $dateDiff: {
                            startDate: {
                              $toDate: "$$this.birthday"
                            },
                            endDate: "$$NOW",
                            unit: "year"
                          }
                        },
                        18
                      ]
                    },
                    "adult",
                    "$$REMOVE"
                  ]
                }
              },
              "$$this"
            ]
          }
        }
      }
    }
  }
])

Mongo Playground

I've used some version 5+ operators like $dateDiff as it makes the code cleaner, but you could still achieve the same results without them using $subtract and a constant for 18 years, like so:

{
    $lt: [
        {
            $toDate: "$$this.birthday"
        },
        {
            $subtract: [
                "$$NOW",
                567648000000// 18 years in miliseconds

            ]
        }
    ]
}

Mongo Playground

user_107703700

It would have worked like this if your date was already in the right format. Since you need to format it, I think you should use an aggregation pipeline with a $merge stage:

db.collection.aggregate([
  {$set: {
      citizens: {
        $map: {
          input: "$citizens",
          in: {$mergeObjects: [
              {status: {
                  $cond: [
                    {$lt: [{$toDate: "$$this.birthday"}, 18yearsaAgoDate]},
                    "adult",
                    "$$REMOVE"
                  ]
                }
              },
              "$$this"
            ]
          }
        }
      }
    }
  },
  { $merge : { into : "collection" } }
])

See how it works on the playground example

user_184823100

This is an update using the arrayFilters syntax.

db.collection.updateMany(
  { }, 
  {
      $set: { "citizens.$[elem].status": "adult" }
  },
  {
      arrayFilters: [ { "elem.status": { $exists: false } , "elem.birthday": { $lt: "2004/07/27" }  } ]
  }
)

Note the date value "2004/07/27" is the day 18 years ago (very close approximate value). And using string values in date comparison requires that the value is formatted in "YYYY/mm/dd".

user_46793200
Related