Date comparison (probably referencing) casting problem on object array inside document

Bilal_Eyrik · 2024-03-30T06:06:01.905Z

Hello Dear Mongodb Community , (schema attached below) I am working on a chat application with the following requirement ; how to query for inbox documents where user1 is a participant & the matching document that has a deleted_by object inside that belongs to user1 has also have the deleted_at date less than inbox.last_activity_date , now the functionality is to bring inboxes for a user where they deleted it , and when a new activity happens after than they deleted the document it must still return , like imagine in whatsapp you delete a chat and when there is a new message there (new activity) , you will still retrieve it since the date you deleted was before the latest even happening. Now when I try the following query (removed the part that matches the user_id since its not the problem) I get the error:

`
Cast to date failed for value “$last_activity_date” (type string) at path “deleted_at” for model “Inbox”
``

now am I not referencing the inbox.last_activity_date correctly here ? since its type date and the actual document saved in db has also type date and I use strict typing so I dont think the casting or field type is the problem nevertheless I can’t figure it out.

Preferably , I do not want to use aggregation pipeline in the code and more elegant approach than mine is always welcome…

I’d appreciate your help , thank you.

const inboxes = await Inbox.find({ 'deleted_by.deleted_at': { $lt: '$last_activity_date' } })

const inboxSchema = new mongoose.Schema({
    participants: [{
        type: mongoose.Schema.Types.ObjectId,
        ref: 'User',
    }],
    last_activity_date: {
        type: Date,
        default: Date.now
    },
    deleted_by: [{
        user: { type: mongoose.Types.ObjectId, ref: 'User' },
        deleted_at: { type: Date, default: Date.now }
    }],
},{ strict: true });

Bilal_Eyrik · 2025-04-12T13:39:47.665Z

Update → MongoDB community is one of the worse communities online , thanks for nothing.

Michael_Lynn · 2025-04-12T13:47:53.614Z

Wow - I’m so sorry… I should have seen this and I didn’t… I’m truly sorry you’ve had this experience with the forums… You’re right tho - we should have responded… this is a failure. I know it’s probably too late now but please allow me to provide something toward a resolution.

So… diving into the issue… seems like the root of the problem is that you’re trying to compare a field (deleted_by.deleted_at) to another field (last_activity_date) directly in a query filter, like:

{ 'deleted_by.deleted_at': { $lt: '$last_activity_date' } }

I think the only, and probably best way to do this is in an aggregation pipeline, not in standard Mongoose .find() queries. That’s why you’re seeing that cast error… I’m pretty sure Mongoose is trying to interpret "$last_activity_date" as a literal string and trying to cast it to a date.

If you’re trying to keep it elegant and avoid an aggregation pipeline… well TBH, you’re kinda stuck… this type of “compare field A to field B within the same document” isn’t doable with a basic .find().

What you can do:

Use aggregation, despite the preference to not:

const inboxes = await Inbox.aggregate([
  {
    $match: {
      participants: user1Id, // assuming you've already got that
    }
  },
  {
    $match: {
      deleted_by: {
        $elemMatch: {
          user: user1Id,
          deleted_at: { $lt: "$last_activity_date" }
        }
      }
    }
  }
]);

Alternative if you really wanna stick to .find(): You’d need to do filtering in code after fetching… for instance, grab inboxes where the user is a participant and then filter manually in JS based on the comparison of deleted_at < last_activity_date.

But IMHO, the aggregation pipeline is cleaner and avoids over-fetching + post-processing in app code.

Once again, I’m really sorry we missed this one… and I hope you’re able to use this to fix the issue.