Identify invalid dates from date string in double nested objects in arrays

Question

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

nimrod serok · Accepted Answer · 2022-10-13 12:47:08Z

1

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

edited Oct 13, 2022 at 12:47

answered Oct 13, 2022 at 11:57

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

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

2 Comments

R2D2 Over a year ago

"errmsg" : "Unrecognized expression '$first' in mongodb 4.2

nimrod serok Over a year ago

Updated the answer accordingly

Collectives™ on Stack Overflow

Identify invalid dates from date string in double nested objects in arrays

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related