Mongodb:按数组最后一个元素中的值对文档进行排序 [英] Mongodb: sort documents by value in the last element of an array
问题描述
我有一个集合collectionName",里面有很多这样的文档:
I have a collection 'collectionName' with many documents like these:
{
"_id" : ObjectId("5f072d45856bc306147c1dcd"),
"readOnly" : false,
"participants" : [
{
"clientType" : "support",
"docId" : "",
"metaData" : {
"fullName" : "Support",
"email" : "Support@mydomain.com",
"phoneNum" : "+1234567890"
}
},
{
"clientType" : "worker",
"docId" : "5e21c48ee178473be81e032e",
"metaData" : {
"fullName" : "Rami",
"email" : "myemail@gmail.com",
"phoneNum" : "+1234567890"
}
}
],
"messages" : [
{
"id" : "IGK-fIt-2zz",
"sender" : "support, ",
"body" : "CONTROL: Agent \"Greendizeriii\" has answered the conversation",
"commData" : {
"sent" : {
"state" : true,
"dateTime" : ISODate("2020-07-11T06:33:48.348Z")
},
"delivered" : {
"state" : false
},
"seen" : {
"state" : true,
"dateTime" : ISODate("2020-07-11T06:40:06.851Z")
}
}
},
{
"id" : "L0f-hwj-QUL",
"sender" : "support, ",
"body" : "Hello rami, How can I help you today",
"commData" : {
"sent" : {
"state" : true,
"dateTime" : ISODate("2020-07-11T06:35:57.406Z")
},
"delivered" : {
"state" : false
},
"seen" : {
"state" : true,
"dateTime" : ISODate("2020-07-11T06:40:06.851Z")
}
}
},
{
"id" : "n0k-s3u-UmN",
"sender" : "support, ",
"body" : "are you still there ?",
"commData" : {
"sent" : {
"state" : true,
"dateTime" : ISODate("2020-07-11T06:36:13.350Z")
},
"delivered" : {
"state" : false
},
"seen" : {
"state" : true,
"dateTime" : ISODate("2020-07-11T06:40:06.851Z")
}
}
}
],
"controlData" : {
"isReserved" : {
"state" : false,
"reservedUntil" : null,
"operatorId" : null
}
},
"__v" : 0
},
{
"_id" : ObjectId("5f1f143f4134643044638902"),
"readOnly" : false,
"participants" : [
{
"clientType" : "support",
"docId" : "",
"metaData" : {
"fullName" : "Support",
"email" : "Support@mydomain.com",
"phoneNum" : "+1234567890"
}
},
{
"clientType" : "worker",
"docId" : "5ea9409ff3243f483c2483ed",
"metaData" : {
"fullName" : "Ronaldo",
"email" : "dummyemail02@gmail.com",
"phoneNum" : "+1234567890"
}
}
],
"messages" : [
{
"id" : "uto-qQb-0cr",
"sender" : "support, ",
"body" : "CONTROL: Agent \"Greendizeriii\" has answered the conversation",
"commData" : {
"sent" : {
"state" : true,
"dateTime" : ISODate("2020-07-27T17:51:59.753Z")
},
"delivered" : {
"state" : false
},
"seen" : {
"state" : true,
"dateTime" : ISODate("2020-07-27T17:52:01.240Z")
}
}
},
{
"id" : "FQg-fSQ-jQ1",
"sender" : "support, ",
"body" : "jjj",
"commData" : {
"sent" : {
"state" : true,
"dateTime" : ISODate("2020-07-27T17:51:59.751Z")
},
"delivered" : {
"state" : false
},
"seen" : {
"state" : true,
"dateTime" : ISODate("2020-07-27T17:52:01.240Z")
}
}
},
{
"id" : "Uqm-J4S-LUT",
"sender" : "support, ",
"body" : "CONTROL: Agent \"Greendizeriii\" has left the conversation",
"commData" : {
"sent" : {
"state" : true,
"dateTime" : ISODate("2020-07-27T17:53:05.142Z")
},
"delivered" : {
"state" : false
},
"seen" : {
"state" : true,
"dateTime" : ISODate("2020-07-27T17:53:05.558Z")
}
}
},
{
"id" : "gs1-Qqz-jkI",
"sender" : "worker, 5ea9409ff3243f483c2483ed",
"body" : "Hello",
"commData" : {
"sent" : {
"state" : true,
"dateTime" : ISODate("2020-07-25T03:58:50.600Z")
},
"delivered" : {
"state" : false
},
"seen" : {
"state" : false
}
}
}
],
"controlData" : {
"isReserved" : {
"state" : false,
"reservedUntil" : null,
"operatorId" : null
}
},
"__v" : 0
}
例如这里有两个文件
每个都有 messages 字段,它是一组元素/对象
each one has messages field which is an array of elements/objects
我想使用消息数组的最新元素的 (commData.sent.dateTime
) 对它们进行排序.
I want to sort them using (commData.sent.dateTime
) of the latest element of messages array.
我知道我可以这样做:
db.getCollection('collectionName').find({}).sort({"messages.0.commData.sent.dateTime": 1})
将返回使用消息数组中第一个元素的 commData.sent.dateTime 排序的文档.但我不知道在这种特殊情况下如何使用数组中的最后一个元素进行排序.
which will return documents sorted using the commData.sent.dateTime of first element in messages array. but I don't know how to sort using the last element in the array in this particular situation.
注意:问题答案:如何排序使用数组最后一个元素的集合在我的情况下将不起作用,因为我需要用来对文档进行排序的字段 (dateTime) 位于嵌套对象内
Note: The answer for question : How to sort a collection using the last element of an array will not work in my case since the field (dateTime) I need to use to sort the documents is inside a nested object
非常感谢您的帮助.
谢谢
推荐答案
您可以使用 $addFields 计算最后发送日期.$arrayElemAt 需要 -1
作为表示数组最后一项的参数:
You can use $addFields to calculate last sent date. $arrayElemAt takes -1
as a parameter which represents the last item of an array:
db.collection.aggregate([
{
$addFields: {
lastSent: {
$let: {
vars: {
last: {
$arrayElemAt: [ "$messages", -1 ]
}
},
in: "$$last.commData.sent.dateTime"
}
}
}
},
{ $sort: { lastSent: 1 } },
{ $project: { lastSent: 0 } }
])
这篇关于Mongodb:按数组最后一个元素中的值对文档进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!