Group by reduced field depending on variable in mongodb

Question

I have the following collection for messages:

{
    "_id" : ObjectId("56214d5632001bae07a6e6b3"),
    "sender_id" : 8,
    "receiver_id" : 2,
    "content" : "fdgfd",
    "state" : 1,
    "timestamp" : 1445023062899.0000000000000000
},
{
    "_id" : ObjectId("56214d5c32001bae07a6e6b4"),
    "sender_id" : 2,
    "receiver_id" : 8,
    "content" : "fasfa",
    "state" : 1,
    "timestamp" : 1445023068443.0000000000000000
},
{
    "_id" : ObjectId("56214d8032001bae07a6e6b5"),
    "sender_id" : 2,
    "receiver_id" : 8,
    "content" : "dfdsfds",
    "state" : 1,
    "timestamp" : 1445023104363.0000000000000000
},
{
    "_id" : ObjectId("56214d8032001bae07a6e6b6"),
    "sender_id" : 2,
    "receiver_id" : 8,
    "content" : "fdsf",
    "state" : 1,
    "timestamp" : 1445023104825.0000000000000000
},
{
    "_id" : ObjectId("56214d8132001bae07a6e6b7"),
    "sender_id" : 2,
    "receiver_id" : 8,
    "content" : "sfsdfs",
    "state" : 1,
    "timestamp" : 1445023105436.0000000000000000
},
{
    "_id" : ObjectId("56214d8132001bae07a6e6b8"),
    "sender_id" : 2,
    "receiver_id" : 8,
    "content" : "f",
    "state" : 1,
    "timestamp" : 1445023105963.0000000000000000
},
{
    "_id" : ObjectId("56214d8432001bae07a6e6b9"),
    "sender_id" : 2,
    "receiver_id" : 8,
    "content" : "qwqwqwq",
    "state" : 1,
    "timestamp" : 1445023108202.0000000000000000
},
{
    "_id" : ObjectId("56214db032001bae07a6e6ba"),
    "sender_id" : 9902,
    "receiver_id" : 2,
    "content" : "fsafa",
    "state" : 1,
    "timestamp" : 1445023152297.0000000000000000
}

I'm trying to get all unique users ids that had been messaging with user 2, along with the last content message. So the result should be:

[ { user: 8, lastContent: "qwqwqwq" }, { user: 9902, lastContent: "fsafa" } ] 

By now, I have the following code:

db.getCollection('messenger').group({
keyf: function(doc) {
    return { user: doc.user };
},
cond: {
    $or : [
        { sender_id : 2 },
        { receiver_id : 2 }
    ]
},
reduce: function( curr, result ) {
    result.user = (curr.sender_id == 2 ? curr.receiver_id : curr.sender_id);
    result.content = curr.content;
 },
 initial: { } })

But I only get the last id. The result:

{
"0" : {
    "user" : 9902.0000000000000000,
    "content" : "fsafa"
} }

Can anyone help me with this?

Answer 1

You need to use the .aggregate() method. You need to reduce the size of documents in the pipeline using the $match operator which filter out all documents where the receiver_id is not equal to 2. After that you need to $sort your document by timestamp in descending order this will help us get the content of last message sent. Now comes the $group stage where you group your documents and use the $addToSet operator which returns array of distinct sender_id and distinct receiver_id and the $last operator to get the last message content. Now to get the user_ids we need union of distinct sender_id and receiver_id which we can get after $projection using the $setUnion operator.

db.messenger.aggregate([ 
    { "$match": { 
        "$or": [ 
            { "sender_id": 2 }, 
            { "receiver_id": 2 } 
        ] 
    }},
    { "$sort": { "timestamp": 1 } },  
    { "$group": { 
        "_id": null, 
        "receiver_id": { 
            "$addToSet": { "$receiver_id" }
        },
        "sender_id": { 
            "$addToSet": { "$sender_id" }
        }, 
        "lastContent": { "$last": "$content" } 
    }},
    { "$project": { 
        "_id": 0, 
        "lastContent": 1, 
        "user_ids": { 
            "$setUnion": [ 
                "$sender_id", 
                "$receiver_id" 
            ] 
        }
    }}
])

Which returns:

{ "lastContent" : "fsafa", "user_ids" : [ 9902, 2, 8 ] }

Now if what you want is distinct user alongside their last content message with user 2 then here it is:

db.messenger.aggregate([ 
    { "$match": { 
        "$or": [ 
            { "sender_id": 2 }, 
            { "receiver_id": 2 } 
        ] 
    }},
    { "$sort": { "timestamp": 1 } },  
    { "$group": { 
        "_id": { 
            "sender": "$sender_id", 
            "receiver": "$receiver_id"
        }, 
        "lastContent": { 
            "$last": "$content"
        }, 
        "timestamp": { "$last": "$timestamp" }, 
        "sender": { "$addToSet": "$sender_id" }, 
        "receiver": { "$addToSet": "$receiver_id" }
    }}, 
    { "$project": {
        "_id": 0, 
        "user": { 
            "$setDifference": [ 
                { "$setUnion": [ "$sender", "$receiver" ] }, 
                [ 2 ] 
            ]
        }, 
        "lastContent": 1,  
        "timestamp": 1 
    }}, 
    { "$unwind": "$user" },
    { "$sort": { "timestamp": 1 } }, 
    { "$group": { 
        "_id": "$user", 
        "lastContent": { "$last": "$lastContent" }
    } }
])

Which yields:

{ "_id" : 9902, "lastContent" : "fsafa" }
{ "_id" : 8, "lastContent" : "qwqwqwq" }