Find all unread conversations in mongodb

Question

I am making a group chat conversation schema using mongoose but I have been having trouble with a query for the past few days. My goal here is to find all the unread conversations of a user.

My schema looks like this:

const ConversationSchema = new Schema(
  {
    participants: [{
      user: mongoose.Schema.Types.ObjectId,
      lastViewed: Date, // the last date the user checked the conversation
    }],
    updatedAt: Date,
  }
);

And this is the simplified sample data:

   {
     participants: [
       {
         user: 'A',
         lastViewed: 'Jan 11'
       },
       {
         user: 'B',
         lastViewed: 'Jan 13'
       },
     ],
     updatedAt: 'Jan 13',
   }

For me, a conversation would be considered unread for a user if:

the user is in the participant array
and the conversation updatedAt value is greater than the participant's lastViewed value

Lastly, my mongoose find function looks like this

const conversations = await conversationModel.find({
  'participants.user': user.id,
  $expr: {
    $gt: ['updatedAt', 'participants.user.lastViewed'],
  },
});

So when I try to query for user B's unread conversations, I should be expecting an empty [] result as Jan 13 = Jan 13 but I am still getting the conversation in the results. I think the problem is in the $expr argument but I am unsure what to add as it deals with an array. Any help would be greatly appreciated.

user_6238977 · Answer

You need to use dollar sign within $expr when referring fields but this won't solve your issue. The problem is that you're trying to find particular user so there can be a situation when user A exists but lastViewed of other user is lower than updatedAt (separate conditions).

It's better to use $let and $filter to find your user first and then apply your conditions on that particular subdocument:

db.collection.find({
    $expr: {
        $let: {
            vars: { user: { $arrayElemAt: [ { $filter: { input: "$participants", cond: { $eq: [ "$$this.user", "A" ] } } }, 0 ] }},
            in: {
                $and: [
                    { $ne: [ "$$user", null ] },
                    { $gt: [ "$updatedAt", "$$user.lastViewed" ] }
                ]
            }
        }
    }
})

Mongo Playground

I would also recommend to convert your dates from strings to ISODate formats to avoid any issues like Jan 13 vs Jan 9 comparison.

Find all unread conversations in mongodb

1 Answers