So, there is a field which is an array of objects of keys "date", "duration", and "equipment". I want to filter that array of objects according the date using aggregation pipeline

my document looks like this,

{
  "_id": "663532074bde5df094686ef3",
  "username": "batman",
  "log": [
    {
      "equipment": "barbell",
      "duration": 30,
      "date": "Sat May 04 2024"
    },
    {
      "equipment": "dumbbell",
      "duration": 12,
      "date": "Mon Jan 01 2012"
    },
    {
      "equipment": "fly machine",
      "duration": 12,
      "date": "Fri Nov 11 2011"
    }
  ],
  "count": 3
}

the date is a string, not of type Date. And my aggregation pipeline looks like this:

[
  {
    $match: {
      _id: "663532074bde5df094686ef3"
    }
  },
  {
    $addFields: {
      log: {
        $filter: {
          input: "$log",
          as: "eachLog",
          cond: {
            $lt: [
              new Date("$$eachLog.date"),
              new Date("2024-01-01")
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      count: {
      	$size: "$log"
      }
    }
  }
]

How do I achieve that ?

Hello @Sandil_Adhikari, Welcome to the MongoDB community forum,

You can not use the javascript functions directly inside your query to do operations in existing properties.

You have to use date operators,

  • First of all, you need to remove the day name from the date string, because the query will not identify the exact date, so the $substr operator will remove the first 4 characters from the date property.
  • $dateFromString operator to convert the string date to actual date, there are multiple options available you can check documentation if you want to pass timezone or any specific format to convert the date.
  {
    $addFields: {
      log: {
        $filter: {
          input: "$log",
          as: "eachLog",
          cond: {
            $lt: [
              {
                $dateFromString: {
                  dateString: {
                    $substr: ["$$eachLog.date", 4, -1]
                  }
                }
              },
              new Date("2024-01-01")
            ]
          }
        }
      }
    }
  }

Playground

2 Likes

Thank You very much, I don’t know how long I’d be stuck if not for this reply.
But on further playing around, I also found out that,

dateString: "$$eachLog.date"

works as intended. Thank you once again.

It’s not working, you have to check every test case,
Ex:

Input:

[
  { "date": "Sat May 04 2024" },
  { "date": "Fri Nov 12 2011" }
]

Query:

db.collection.aggregate([
  {
    $addFields: {
      inCorrect: {
        $dateFromString: {
          dateString: "$date"
        }
      },
      correct: {
        $dateFromString: {
          dateString: {
            $substr: ["$date", 4, -1]
          }
        }
      }
    }
  }
])

Result:

[
  {
    "correct": ISODate("2024-05-04T00:00:00Z"),
    "date": "Sat May 04 2024",
    "inCorrect": ISODate("2024-05-04T00:00:00Z")
  },
  {
    "correct": ISODate("2011-11-12T00:00:00Z"),
    "date": "Fri Nov 12 2011",
    "inCorrect": ISODate("2011-11-18T00:00:00Z")
  }
]

Playground

1 Like