CODEWITHSUNDEEP
mongodb
Max Frai
Max Frai

Reputation: 64276

Mongodb: how to group by key and save additional information for items in group

I have messages system in mongodb. Messages object looks like this:

{
    who: String,
    whom: String,
    when: Date
}

So, for example, when user 1 sends message to user 2 I have:

{ who: "1", whom: "2", when: MSG_DATE }

And when user 2 sends message to 1 in response the following row is added:

{ who: "2", whom: "1", when: MSG_DATE }

Now I need to collect unique dialogs in UI for user 1, for example.

[
    {
        "$match": {
            "$or": [
                { "who": "1" },
                { "whom": "1" }
            ]
        }
    },
    {
        "$group": {
            "_id": "a",
            "dialogs_who": { "$addToSet": "$who" },
            "dialogs_whom": { "$addToSet": "$whom" }
        }
    },
    {
        "$project": {
            "name": "$_id",
            "profiles": {
                "$setUnion": [ "$dialogs_who", "$dialogs_whom" ]
            }
        }
    }
]

This will return me : ["1", "2"]. In code I just skip dialog 1 for user 1.

With this aggregation I search for messages where who or whom is user we are loading info for (1). Results are added to set to make it unique and finally two sets are merged into one. It works, bu I need to store latest date message for each dialog.

So if there are such rows in db:

{ who: "1", whom: "2", when: MSG_DATE1 },
{ who: "2", whom: "1", when: MSG_DATE2 },
{ who: "3", whom: "1", when: MSG_DATE3 },
{ who: "3", whom: "4", when: MSG_DATE4 }    // It's not dialog with 1

Here we have two unique dialogs (minus dialog with 1) for user 1: ["1", "2", "3"]. How to add into aggregation storing of latest when date for dialog with.

So if MSG_DATE2 > MSG_DATE_1 I want result like:

[
    "2": MSG_DATE2,
    "3": MSG_DATE4
}

Upvotes: 0

Views: 73

Answers (1)

YuTing
YuTing

Reputation: 6629

This is the most appropriate answer I can think of. It may be long, but the logic is correct.

Test date:

/* 1 */
{
    "_id" : ObjectId("612630679bd62cd759b1df00"),
    "who" : "1",
    "whom" : "2",
    "when" : "20120820"
}

/* 2 */
{
    "_id" : ObjectId("612630679bd62cd759b1df03"),
    "who" : "2",
    "whom" : "1",
    "when" : "20120822"
}

/* 3 */
{
    "_id" : ObjectId("612630679bd62cd759b1df06"),
    "who" : "3",
    "whom" : "1",
    "when" : "20120825"
}

/* 4 */
{
    "_id" : ObjectId("612630679bd62cd759b1df09"),
    "who" : "3",
    "whom" : "4",
    "when" : "20120828"
}

Aggregate:

db.collection.aggregate([
    {
        $match:{"$or":[ { "who":"1"}, {"whom":"1"} ]}
    },
    {
        $project:{ 
            who:{ 
                $first: { 
                    $filter: {
                        input: [ "$who", "$whom" ],
                        as: "i",
                        cond: { $ne: [ "$$i", "1" ] }
                    }
                }
            },
            when:1  
        }
    },
    {
        $sort:{"when":-1}
    },
    {
        $group:{_id:"$who", when:{"$first":"$when"}}
    }
])

Result:

/* 1 */
{
    "_id" : "2",
    "when" : "20120822"
}

/* 2 */
{
    "_id" : "3",
    "when" : "20120825"
}

Upvotes: 1

Related Questions