CODEWITHSUNDEEP
javascriptnode.jsmongodbmongodb-queryaggregation-framework
Ravi
Ravi

Reputation: 1360

MongoDB aggregate and match the returned keys

The thing is, i'm writing some code for getting user messages from my DB. DB has sent_to, sent_by, message keys and the command i'm following to get the data keys is

db.users_messages.aggregate({$group: {_id: {to: "$sent_to", by: "$sent_by"}}})

This returns the list of all the sent_to and sent_by Users_IDs. Now let's suppose if user1 has sent the message to user2 and also user2 has replied back to user1, i want it to return only user1 for user2, not user2 for user1. And when i'm checking this thing later, then i'm left with pagination thing, because i'm applying $limit on aggregation. Hope someone can help!

Upvotes: 1

Views: 332

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

What you are asking is no small feat and I think the real solution here is to include some more meta-data on your documents, specifically to represent who the conversation is "between" in a consistent way.

What I mean by this, is the "key" needs to be unique for the conversation, no matter who was sending the message or who was receiving. Consider these two basic documents.

{ "from": 1, "to": 2, "between": [1,2] },
{ "from": 2, "to": 1, "between": [1,2] }

Where in each case the "from" and "to" are unique identifiers for each "user" that will always sort in a specific way. The "between" data is always sorted to remain in the same order, this could be done in code as you create or via "upsert" functionality with the $sort modifier and $each, but the point is to keep that "unique" key to use in determining which documents belong to the same grouping.

It is possible to do with the aggregation framework alone, but it is really unnecessary hoop jumping when you consider you could just maintain that on your documents:

db.converse.aggregate([
    { "$group": {
        "_id": "$_id",
        "from": { "$push": "$from" },
        "to": { "$push": "$to" }  
    }},
    { "$project": {
        "between": { "$setUnion": [ "$from", "$to" ] }
    }},
    { "$unwind": "$between" },
    { "$sort": { "between": 1 } },
    { "$group": {
        "_id": "$_id",
        "between": { "$push": "$between" }
    }},
    { "$group": {
        "_id": "$between",
        "count": { "$sum": 1 }
    }}
])

In in versions earlier than MongoDB 2.6 that does not have something like **$setUnion available, combine to an array in a different way:

db.converse.aggregate([
    { "$project": {
        "from": 1,
        "to": 1,
        "type": { "$const": [ "from", "to" ] },
    }},
    { "$unwind": "$type" },
    { "$group": {
        "_id": "$_id",
        "between": {
            "$addToSet": {
                "$cond": [
                    { "$eq": [ "$type", "from" ] },
                    "$from",
                    "$to"
                ]
            }
        }
    }},
    { "$unwind": "$between" },
    { "$sort": { "between": 1 } },
    { "$group": {
        "_id": "$_id",
        "between": { "$push": "$between" }
    }},
    { "$group": {
        "_id": "$between",
        "count": { "$sum": 1 }
    }}
])

In each case, there is a reasonable amount of paranoia being exhibited as "sets are not ordered". They may happen to come out that way, but you probably cannot count on that.

The principles are basically the same here, by creating a uniquely ordered "list" in the "between" element and then using that as the grouping key. From the above example document, and ignoring the existing between field, the process will return just one document with a count of "two" as shown:

{ "_id" : [ 1, 2 ], "count" : 2 }

So it really makes a lot of sense to maintain this sort of data on the document as you create or modify them. In that way the grouping becomes simple as the "unique key" is already identified

Upvotes: 1

Related Questions