I need to identify all invalid dateP:

 {
   "_id": ObjectId("5c05984246a0201286d4b57a"),
  f: "x",
  "_a": [
  {
    "_onlineStore": {}
  },
  {
    "_p": {
      "s": {
        "a": {
          "t": [
            {
              "dateP": "9999-09-20",
              "l": "English",
              "size": "XXL"
            }
          ]
        },
        "c": {
          "t": [
            {
              "dateP": "2020-09-20",
              "l": "English",
              "size": "XXL"
            }
          ]
        }
      }
    }
    }
   ]
  }

expected output:

 {f:"x",dateP:"9999-09-20", t:"a"}

Valid date is considered anything between: 1900-01-01 ... 2023-01-01 also it need to be valid: year: 1900-2023 month: 01-12 day: 01-31

Playground

CodePudding user response：

According to your definition of "valid" we can do:

db.collection.aggregate([
  {$unwind: "$_a"},
  {$project: {_id: 0, f: 1, data: {$objectToArray: "$_a._p.s"}}},
  {$unwind: "$data"},
  {$unwind: "$data.v.t"},
  {$set: {dateParts: {"$split": ["$data.v.t.dateP", "-"]}}},
  {$project: {
      year: {$toInt: {$arrayElemAt: ["$dateParts", 0]}},
      month: {$toInt: {$arrayElemAt: ["$dateParts", 1]}},
      day: {$toInt: {$arrayElemAt: ["$dateParts", 2]}},
      data: 1
  }},
  {$match: {
      $expr: {$or: [
          {$lt: ["$year", 1900]},
          {$gt: ["$year", 2022]},
          {$lt: ["$month", 1]},
          {$gt: ["$month", 12]},
          {$lt: ["$day", 1]},
          {$gt: ["$day", 31]}
        ]
      }
    }
  },
  {$project: {f: 1, dateP: "$data.v.t.dateP", t: "$data.k"}}
])

See how it works on the playground example