I've got some data stored in a collection, something like this (the date is stored in a string unfortunately):

_id: ObjectId('55663d59148f358b6582d902'),
order_no: '123456',
results: [
  {a: 'order_info', b: 'description', result: 'repeat order'},
  {a: 'order_info', b: 'date', result: '2022-10-20T23:00:00.000Z'},
  {a: 'customer', b: 'name', result: 'Mickey Mouse'},
  {a: 'patient', b: 'name', result: 'Daffy Duck'}
]

I'm looking for a way to get results which are

1. repeat orders
2. created after a given date (ideally I don't want to convert all the dates).

I've tried the following but this doesn't yield any results.

db.orders.find(
  { results: [
    {a: 'order_info', b: 'description', result: 'repeat order'}
    {a: 'order_info', b: 'date', result: { $gte: '2022-01-01T00:00:00:000Z' } }
  ]
  }
)

CodePudding user response：

You can use $elemMatch to do this, like so:

db.collection.find({
  results: {
    "$elemMatch": {
      a: "order_info",
      $or: [
        {
          result: {
            $gt: "2022-01-01T00:00:00:000Z",
            $lt: "2099-01-01T"
          }
        },
        {
          result: "repeat order"
        }
      ]
    }
  }
})

Mongo Playground

The problem is with the "date" string comparison, as you can see I added a $lt because the string "abc" matches the $gte operator vs "'2022-01-01T00:00:00:000Z'" as any alphabetic char is "bigger" than a numeric and you are doing a string comparison.

This will still match a string like "2055 is the best year ever", the best way to avoid this is to just update the db to use Dates instead, otherwise you can match a regex as well to ensure it's a "valid" date.