mongoDB find document greatest date and check value

Question

I have a Conversation collection that looks like this:

[
  {
    "_id": "QzuTQYkGDBkgGnHrZ",
    "participants": [
      {
        "id": "YymyFZ27NKtuLyP2C"
      },
      {
        "id": "d3y7uSA2aKCQfLySw",
        "lastVisited": "2016-02-04T02:59:10.056Z",
        "lastMessage": "2016-02-04T02:59:10.056Z"
      }
    ]
  },
  {
    "_id": "e4iRefrkqrhnokH7Y",
    "participants": [
      {
        "id": "d3y7uSA2aKCQfLySw",
        "lastVisited": "2016-02-04T03:26:33.953Z",
        "lastMessage": "2016-02-04T03:26:53.509Z"
      },
      {
        "id": "SRobpwtjBANPe9hXg",
        "lastVisited": "2016-02-04T03:26:35.210Z",
        "lastMessage": "2016-02-04T03:15:05.779Z"
      }
    ]
  },
  {
    "_id": "twXPHb76MMxQ3MQor",
    "participants": [
      {
        "id": "d3y7uSA2aKCQfLySw"
      },
      {
        "id": "SRobpwtjBANPe9hXg",
        "lastMessage": "2016-02-04T03:27:35.281Z",
        "lastVisited": "2016-02-04T03:57:51.036Z"
      }
    ]
  }
]

Each conversation (document) can have a participant object with the properties of id, lastMessage, lastVisited.

Sometimes, depending on how new the conversation is, some of these values don't exist just yet (such as lastMessage, lastVisited).

What I'm trying to do is compare each participant in each individual conversation (document) and see if out of the all the participants, the greatest lastMessage field value belongs to the logged in user. Otherwise, I'm assuming that the conversation has messages that the logged in user hasn't seen yet. I want to get that count of messages that the user possibly hasn't seen yet.

In the example above, say we're logged in as d3y7uSA2aKCQfLySw. We can see that he was the last person to send a message for conversation 1, 2 BUT not 3. The count returning for how many updated conversations that d3y7uSA2aKCQfLySw hasn't seen should be 1.

Can someone point me in the right direction? I haven't the slightest clue as to how to approach the issue. My apologies for the lengthy question.

Answer 1

I'd like to try this function.

function findUseen(uId) {
    var numMessages = db.demo.aggregate(
        [
            {
                $project: {
                    "participants.lastMessage": 1,
                    "participants.id": 1
                }
            },
            {$unwind: "$participants"},
            {$sort: {"participants.lastMessage": -1}},
            {
                $group: {
                    _id: "$_id",
                    participantsId: {$first: "$participants.id"},
                    lastMessage: {$max: "$participants.lastMessage"}
                }
            },
            {$match: {participantsId: {$ne: uId}}},
        ]
    ).toArray().length;

    return numMessages;
}

calling findUnseen("d3y7uSA2aKCQfLySw") will return 1.

I have adopted this function just to return count, but as you see it's easy to tweak it to return all unseen message metadata too.

Answer 2

It is always advisable to store dates as ISODate rather than strings to leverage the flexibility provided by various date operators in the aggregation framework.

One way of getting the count is to,

• $match the conversations in which the user is involved.
• $unwind the participants field.
• $sort by the lastMessage field in descending order
• $group by the _id to get back the original conversations intact, and get the latest message per group(conversation) using the $first operator.
• $project a field with value 0, for each group where the top most record is of the user we are looking for and 1 for others.
• $group again to get the total count of the conversations in which he has not been the last one to send a message.

sample code:

var userId = "d3y7uSA2aKCQfLySw"; 
db.t.aggregate([
 {
    $match:{"participants.id":userId}
 },
 {
    $unwind:"$participants"
 },
 {
    $sort:{"participants.lastMessage":-1}
 },
 {
    $group:{"_id":"$_id","lastParticipant":{$first:"$$ROOT.participants"}}
 },
 {
    $project:{
   "hasNotSeen":{$cond:[
                         {$eq:["$lastParticipant.id",userId]},
                         0,
                         1
                       ]},
   "_id":0}
 },
 {
    $group:{"_id":null,"count":{$sum:"$hasNotSeen"}}
 },
 {
    $project:{"_id":0,"numberOfConversationsNotSeen":"$count"}
 }
])