Mongo query and response 'GroupBy' implementation using Mongoose and/or underscore.js

Question

We have a mongo collection messages that is in the following format...

/* 0 */
{
  "text" : "A message to John",
  "created" : ISODate("2015-01-29T23:50:10.488Z"),
  "to" : {
    "id" : ObjectId("54bf1dc6c65b030c00faec0d"),
    "name" : "John Smith"
  },
  "from" : {
    "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
    "name" : "Martin Fowler"
  },
}
/* 1 */
{
  "text" : "Another message to John",
  "created" : ISODate("2015-01-30T00:37:38.106Z"),
  "to" : {
    "id" : ObjectId("54bf1dc6c65b030c00faec0d"),
    "name" : "John Smith"
  },
  "from" : {
    "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
    "name" : "Martin fowler"
  },
}

/* 2 */
{
  "text" : "Just another message to Jerry",
  "created" : ISODate("2015-01-30T00:37:38.106Z"),
  "to" : {
    "id" : ObjectId("54bf1e80eb5bf8800b0f5a8d"),
    "name" : "Jerry Jones"
  },
  "from" : {
    "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
    "name" : "Martin Fowler"
  },
}
/* 2 */
{
  "text" : "Message to Martin Fowler",
  "created" : ISODate("2015-01-30T00:37:38.106Z"),
  "to" : {
    "id" : ObjectId("54bf1e80eb5bf8800b0f5a8d"),
    "name" : "Martin Fowler"
  },
  "from" : {
    "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
    "name" : "Jerry Jones"
  },
}

We need to display the data above in a conversation format where we group each of the conversations together. A conversation meaning that it can either be to or from.

An example of what we need this to data to look like is the following format:

{  
   "result":[  
      {  
         "id":"54bf1dc6c65b030c00faec0d",
         "name":"Jerry Jones",
         "messages":[  
            {
              "text" : "A message to John",
              "created" : ISODate("2015-01-29T23:50:10.488Z"),
              "to" : {
                "id" : ObjectId("54bf1dc6c65b030c00faec0d"),
                "name" : "John Smith"
              },
              "from" : {
                "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
                "name" : "Martin Fowler"
              },
            }
            {
              "text" : "Another message to John",
              "created" : ISODate("2015-01-30T00:37:38.106Z"),
              "to" : {
                "id" : ObjectId("54bf1dc6c65b030c00faec0d"),
                "name" : "John Smith"
              },
              "from" : {
                "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
                "name" : "Martin fowler"
              },
            }
         ]
      },
      {  
         "id":"54bf1e80eb5bf8800b0f5a8d",
         "name":"John Smith",
         "messages":[  
            {
              "text" : "Just another message to Jerry",
              "created" : ISODate("2015-01-30T00:37:38.106Z"),
              "to" : {
                "id" : ObjectId("54bf1e80eb5bf8800b0f5a8d"),
                "name" : "Jerry Jones"
              },
              "from" : {
                "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
                "name" : "Martin Fowler"
              },
            }
            {
              "text" : "Message to Martin Fowler",
              "created" : ISODate("2015-01-30T00:37:38.106Z"),
              "to" : {
                "id" : ObjectId("54bf1e80eb5bf8800b0f5a8d"),
                "name" : "Martin Fowler"
              },
              "from" : {
                "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
                "name" : "Jerry Jones"
              },
            }
         ]
      }
   ]
}

NOTE This query is always made in the context of a current user which is why on each of the conversations, we have an _id and a name which represents the other user in the conversation. For the example above, the current user would be Martin Fowler

We have tried several different ways of accomplishing this, but keep running into issues. Id rather turn to the Mongo/Node community in order to see how it can be done correctly. We are using mongoose with Node if that helps at all...

The initial query implementation that we have currently is the following:

Message.find().or([{'from.id':req.user.id},{'to.id':req.user.id}]).exec(function(err,messages){

    //NEED TO IMPLEMENT THIS HERE CORRECTLY
});

NOTE that the req.user.id is the id of the current user

score 1 · Accepted Answer · edited May 23 '17 at 12:27

This solution is a slight modification of my previous solution to a similar kind of problem here.

The documents in question have member names in inconsistent cases. Martin Folwer should be Martin Fowler throughout the app/database, and not Martin fowler. (Note the small f). You need to make this change to your documents.

$group messages together, based on the to and from field. construct a group key - "message_between", with the value being a $concat result of the values in to and from fields.
Messages from Martin Fowler to Jerry Jones and Jerry Jones to Martin Fowler should be treated under a single group.To achieve that, we make the result contain the name coming last alphabetically, first. So our key for all the Messages from Martin Fowler to Jerry Jones and Jerry Jones to Martin Fowler would be Martin Fowler and Jerry Jones.

Code:

Model.aggregate(
//match all those records which involve the user.
{$match:{$or:[{"to.name":req.user.id},
              {"from.name":req.user.id}]}},
{$group:{"_id":{
    "message_between":{
        $cond:[
            {
                $gt:[
                {$substr:["$to.name",0,1]},
                {$substr:["$from.name",0,1]}]
            },
            {$concat:["$to.name"," and ","$from.name"]},
            {$concat:["$from.name"," and ","$to.name"]}
        ]
    },"name":{$cond:[{$eq:["$to.name",
                           req.user.id]},
                     "$from.name",
                     "$to.name"]}
    },"messages":{$push:"$$ROOT"}
    }
},
{$project:{"_id":0,"name":"$_id.name","messages":1}}
,function(err,resp){
   // handle response.
})

o/p:

{
        "messages" : [
                {
                        "_id" : ObjectId("54d1d819b62f332e93fbb906"),
                        "text" : "Just another message to Jerry",
                        "created" : ISODate("2015-01-30T00:37:38.106Z"),
                        "to" : {
                                "id" : ObjectId("54bf1e80eb5bf8800b0f5a8d"),
                                "name" : "Jerry Jones"
                        },
                        "from" : {
                                "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
                                "name" : "Martin Fowler"
                        }
                },
                {
                        "_id" : ObjectId("54d1d819b62f332e93fbb907"),
                        "text" : "Message to Martin Fowler",
                        "created" : ISODate("2015-01-30T00:37:38.106Z"),
                        "to" : {
                                "id" : ObjectId("54bf1e80eb5bf8800b0f5a8d"),
                                "name" : "Martin Fowler"
                        },
                        "from" : {
                                "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
                                "name" : "Jerry Jones"
                        }
                }
        ],
        "name" : "Jerry Jones"
}
{
        "messages" : [
                {
                        "_id" : ObjectId("54d1d819b62f332e93fbb904"),
                        "text" : "A message to John",
                        "created" : ISODate("2015-01-29T23:50:10.488Z"),
                        "to" : {
                                "id" : ObjectId("54bf1dc6c65b030c00faec0d"),
                                "name" : "John Smith"
                        },
                        "from" : {
                                "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
                                "name" : "Martin Fowler"
                        }
                },
                {
                        "_id" : ObjectId("54d1d819b62f332e93fbb905"),
                        "text" : "Another message to John",
                        "created" : ISODate("2015-01-30T00:37:38.106Z"),
                        "to" : {
                                "id" : ObjectId("54bf1dc6c65b030c00faec0d"),
                                "name" : "John Smith"
                        },
                        "from" : {
                                "id" : ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
                                "name" : "Martin Fowler"
                        }
                }
        ],
        "name" : "John Smith"
}

score 0 · Answer 2 · answered Feb 04 '15 at 06:43

I tried to create a mongo request for you, but i'm not sure it's relevant... I can generate almost exactly the same format as you need in one request:

db.messages.aggregate([{$match:{$or:[{"from.id":ObjectId("54bf1dc6c65b030c00faec0d")},{"to.id":ObjectId("54bf1dc6c65b030c00faec0d")}]}},{$group:{_id: ObjectId("54bf1dc6c65b030c00faec0d"), messages:{$push: "$$ROOT"}}}]);

The probleme is, you still need to specify the id, i'm not sure it's what you need.

Using this I get the following result:

{
    "_id": ObjectId("54bf1dc6c65b030c00faec0d"),
    "messages": [
        {
            "_id": ObjectId("54d1b9f02cd45cae7e453633"),
            "text": "A message to John",
            "created": ISODate("2015-01-29T23:50:10.488Z"),
            "to": {
                "id": ObjectId("54bf1dc6c65b030c00faec0d"),
                "name": "John Smith"
            },
            "from": {
                "id": ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
                "name": "Martin Fowler"
            }
        },
        {
            "_id": ObjectId("54d1ba052cd45cae7e453634"),
            "text": "Another message to John",
            "created": ISODate("2015-01-30T00:37:38.106Z"),
            "to": {
                "id": ObjectId("54bf1dc6c65b030c00faec0d"),
                "name": "John Smith"
            },
            "from": {
                "id": ObjectId("54bf1e1ceb5bf8800b0f5a8c"),
                "name": "Martin fowler"
            }
        }
    ]
}

I'm not using mongoose so I can't help you with this part, but with "mongodb" module, this query can be used directly, so it should be possible with mongoose too.

Mongo query and response 'GroupBy' implementation using Mongoose and/or underscore.js

2 Answers2