我试图获取用户之间聊天中的最后一条消息。
以下是我的文档中的收藏集。
{
"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/