我试图获取用户之间聊天中的最后一条消息。
以下是我的文档中的收藏集。

{
"toUser":123,"fromUser":456,"message":"1 from suresh","timeStamp":"2019-10-09 16:39:14:1414 PM +05:30",

"toUser":456,"fromUser":123,"message":"Man super man ","timeStamp":"2019-10-09 16:43:09:0909 PM +05:30",

"toUser":456,"fromUser":123,"message":"Kk","timeStamp":"2019-10-09 18:31:12:1212 PM +05:30"

"toUser":456,"fromUser":123,"message":"It working man","timeStamp":"2019-10-09 18:31:18:1818 PM +05:30"

"toUser":456,"fromUser":123,"message":"2","timeStamp":"2019-10-09 18:31:21:2121 PM +05:30"

"toUser":101,"fromUser":123,"message":"Kk","timeStamp":"2019-10-09 18:31:12:1212 PM +05:30"
}


我用mongo查询为

db.chats.aggregate(
[
    {$match:{$or:[{"toUser":123},{"fromUser":123}]}},
    { "$sort": { "timeStamp": -1}},
    {
        "$group": {
           "_id": {fromUser:"$fromUser",toUser:"$toUser"},
           "fromUser": {"$first":"$fromUser"},
           "toUser" : {"$first":"$toUser"},
           "message": {"$first": "$message" },
            "timeStamp": { "$first": "$timeStamp"}
        }
    },
]
);


我的输出是

{ "_id" : { "fromUser" : 456, "toUser" : 123 }, "fromUser" : 456, "toUser" : 123, "message" : "1 from suresh", "timeStamp" : "2019-10-09 16:39:14:1414 PM +05:30" }
{ "_id" : { "fromUser" : 123, "toUser" : 101 }, "fromUser" : 123, "toUser" : 101, "message" : "Kk", "timeStamp" : "2019-10-09 18:31:12:1212 PM +05:30" }
{ "_id" : { "fromUser" : 123, "toUser" : 456 }, "fromUser" : 123, "toUser" : 456, "message" : "2", "timeStamp" : "2019-10-09 18:31:21:2121 PM +05:30" }



但是,如果您看到123和456个用户之间的实际最后一条消息是message:2

所以我需要最终输出如下,按时间戳排序。


{ "_id" : { "fromUser" : 123, "toUser" : 456 }, "fromUser" : 123, "toUser" : 456, "message" : "2", "timeStamp" : "2019-10-09 18:31:21:2121 PM +05:30" }
{ "_id" : { "fromUser" : 123, "toUser" : 101 }, "fromUser" : 123, "toUser" : 101, "message" : "Kk", "timeStamp" : "2019-10-09 18:31:12:1212 PM +05:30" }


谁能帮助我解决查询。

提前致谢。

最佳答案

问题是消息是如何stored

存储的数据:

fromUser 123 and toUser 456
fromUser 456 and toUser 123


因此,在对它进行分组时,逻辑上它们是相同的,但是not for grouping

解:

我们需要确保发送者/接收者总是以相同的方式出现



1. Create array where we store sender / receiver
2. Order that array `[123, 456] [456, 123] => [123,456], [123,456]`
3. Group by this ordered array (In Mongo way: $project, $unwind, $sort, $group - push)




db.chats.aggregate([
  {
    $match: {
      $or: [
        {
          "toUser": 123
        },
        {
          "fromUser": 123
        }
      ]
    }
  },
  {
    "$project": {
      toUser: 1,
      fromUser: 1,
      message: 1,
      timeStamp: 1,
      fromToUser: [
        "$fromUser",
        "$toUser"
      ]
    }
  },
  {
    $unwind: "$fromToUser"
  },
  {
    $sort: {
      "fromToUser": 1
    }
  },
  {
    $group: {
      _id: "$_id",
      "fromToUser": {
        $push: "$fromToUser"
      },
      "fromUser": {
        "$first": "$fromUser"
      },
      "toUser": {
        "$first": "$toUser"
      },
      "message": {
        "$first": "$message"
      },
      "timeStamp": {
        "$first": "$timeStamp"
      }
    }
  },
  {
    "$sort": {
      "timeStamp": -1
    }
  },
  {
    "$group": {
      "_id": "$fromToUser",
      "fromUser": {
        "$first": "$fromUser"
      },
      "toUser": {
        "$first": "$toUser"
      },
      "message": {
        "$first": "$message"
      },
      "timeStamp": {
        "$first": "$timeStamp"
      }
    }
  }
])




结果



[
  {
    "_id": [
      101,
      123
    ],
    "fromUser": 123,
    "message": "Kk",
    "timeStamp": "2019-10-09 18:31:12:1212 PM +05:30",
    "toUser": 101
  },
  {
    "_id": [
      123,
      456
    ],
    "fromUser": 123,
    "message": "2",
    "timeStamp": "2019-10-09 18:31:21:2121 PM +05:30",
    "toUser": 456
  }
]


MongoPlayground

关于mongodb - 如何从mongodb的聊天对话中获取最新消息,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/58315678/

10-16 21:23
查看更多