CODEWITHSUNDEEP
mongodbmongodb-query
Andra Nico
Andra Nico

Reputation: 23

How to find the most recent item in a group using MongoDB aggregation framework?

I have a collection where I store messages and it looks like this:

id sender receiver date
------------------------
1     1       2    30-May-15 3:14:48 PM
2     2       1    30-May-15 3:16:28 PM
3     1       3    30-May-15 3:20:00 PM
4     1       2    30-May-15 3:21:48 PM
5     3       2    30-May-15 3:25:15 PM
6     4       1    30-May-15 3:30:05 PM

Sender contains the id of the person who sent the message and receiver contains the id of the person who will receive it.

I want to create a list of the most recent contacts. That is, find all the people that a certain person has spoken (as the sender or the receiver), ordered descending by date. In case of duplicates, I need only to keep the most recent contact.

For example: If I search the persons that spoke with the person with id=1 I want to obtain the following person ids: 4, 2, 3(the persons with whom he had spoken): 4 is the latest person (date 30-May-15 3:30:05 PM), 2 was before 4 (date 30-May-15 3:21:48 PM), and 3 before 2(date 30-May-15 3:20:00 PM).

I tried this query:

messages.aggregate({$match: {$or:[{sender: searched_id}, {receiver: searched_id}]}},
                 {$sort: {date: -1}},
                 {$group: {"_id": {sender: "$sender", receiver: "$receiver"}}},
                  function(err, docs){
    console.log(JSON.stringify(docs));
});

This query gives me all the people with who a certain person have spoken, but it's not in the correct order and if I change the sorting order, it gives me the exact result.

What can I do to sort my collection by date?

Upvotes: 2

Views: 1212

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 51980

Given that dataset:

{ "_id" : 1, "sender" : 1, "receiver" : 2, "date" : ISODate("2015-05-30T15:14:48Z") }
{ "_id" : 2, "sender" : 2, "receiver" : 1, "date" : ISODate("2015-05-30T15:16:28Z") }
{ "_id" : 3, "sender" : 1, "receiver" : 3, "date" : ISODate("2015-05-30T15:20:00Z") }
{ "_id" : 4, "sender" : 1, "receiver" : 2, "date" : ISODate("2015-05-30T15:21:48Z") }
{ "_id" : 5, "sender" : 3, "receiver" : 2, "date" : ISODate("2015-05-30T15:25:15Z") }
{ "_id" : 6, "sender" : 4, "receiver" : 1, "date" : ISODate("2015-05-30T15:30:05Z") }

As apparently you want to keep only the most recent contact, you can achieve your desired result by using:

> searched_id = 1
> db.test.aggregate([
 {$match: {$or: [{sender: searched_id}, {receiver: searched_id}]}},
 {$project: { _id: 1, date: 1,
              interlocutor: {$cond: [{$eq: ["$sender", searched_id]},"$receiver","$sender"]}}},
 {$group: {_id: "$interlocutor", date: {$max: "$date"}}},
 {$sort: {date: -1}},
])
  • the $match stage is a simple filter;
  • the $project stage will infer the interlocutor as the sender or receiver;
  • the $group stage will group multiple results per interlocutor, keeping the most recent call ($max) date in each group;
  • finally a $sort stage orders the resulting documents from the most to the least recent.

Running that pipeline, it will return: 

{ "_id" : 4, "date" : ISODate("2015-05-30T15:30:05Z") }
{ "_id" : 2, "date" : ISODate("2015-05-30T15:21:48Z") }
{ "_id" : 3, "date" : ISODate("2015-05-30T15:20:00Z") }

Upvotes: 1

Related Questions