使用基于多级对象值的条件过滤嵌套数组并更新它们-MongoDB聚合+更新 [英] Filter nested array with conditions based on multi-level object values and update them - MongoDB aggregate + update
问题描述
考虑到我的收藏集中有以下文件(忽略_id):
[
{
"Id": "OP01",
"Sessions": [
{
"Id": "Session01",
"Conversations": [
{
"Id": "Conversation01",
"Messages": [
{
"Id": "Message01",
"Status": "read",
"Direction": "inbound"
},
{
"Id": "Message02",
"Status": "delivered",
"Direction": "internal"
},
{
"Id": "Message03",
"Status": "delivered",
"Direction": "inbound"
},
{
"Id": "Message04",
"Status": "sent",
"Direction": "outbound"
}
]
},
{
"Id": "Conversation02",
"Messages": [
{
"Id": "Message05",
"Status": "sent",
"Direction": "outbound"
}
]
}
]
},
{
"Id": "Session02",
"Conversations": [
{
"Id": "Conversation03",
"Messages": [
{
"Id": "Message06",
"Status": "read",
"Direction": "inbound"
},
{
"Id": "Message07",
"Status": "delivered",
"Direction": "internal"
}
]
},
{
"Id": "Conversation04",
"Messages": []
}
]
}
]
},
{
"Id": "OP02",
"Sessions": [
{
"Id": "Session03",
"Conversations": []
}
]
},
{
"Id": "OP03",
"Sessions": []
}
]
第一个查询-聚合(+ $project)
我想获取按其 Conversations 分组的 Messages 列表，其中:
First query — aggregate (+$project)
I want to get the list of Messages grouped by their Conversations where:
-
Sessions.Id: "Session01"
和
-
Sessions.Conversations.Messages.Direction $in ["inbound", "outbound"]
和
-
Sessions.Conversations.Messages.Status $in ["sent", "delivered"]
预期结果是:
[
{
"Id": "Conversation01",
"Messages": [
{
"Id": "Message03",
"Status": "delivered",
"Direction": "inbound"
},
{
"Id": "Message04",
"Status": "sent",
"Direction": "outbound"
}
]
},
{
"Id": "Conversation02",
"Messages": [
{
"Id": "Message05",
"Status": "sent",
"Direction": "outbound"
}
]
}
]
旁注:
A side note:
如果在不同的documents(或不同的Sessions)上，Sessions.Id: "Session01"条件得到验证( "Session01"不是唯一键)，则document'与其他条件匹配的Messages也应添加.
If on different documents (or on different Sessions) the Sessions.Id: "Session01" condition is verified ("Session01"is not an unique key), the document's Messages that match the other conditions should also be added.
结果输出未提及document或Sessions级别.
我要将所有消息(与以前相同) Sessions.Conversations.Messages.Status 更新为 "read" .
I want to update the Sessions.Conversations.Messages.Status of all those messages (same condition as before) to "read".
该收藏集现在应该包含以下文档:
请注意以下更改:
-
Sessions.Conversations.Messages.Id = "Message03" -
Sessions.Conversations.Messages.Id = "Message04" -
Sessions.Conversations.Messages.Id = "Message05"
Sessions.Conversations.Messages.Id = "Message03"Sessions.Conversations.Messages.Id = "Message04"Sessions.Conversations.Messages.Id = "Message05"
at Sessions.Id = "Session01"
[
{
"Id": "OP01",
"Sessions": [
{
"Id": "Session01",
"Conversations": [
{
"Id": "Conversation01",
"Messages": [
{
"Id": "Message01",
"Status": "read",
"Direction": "inbound"
},
{
"Id": "Message02",
"Status": "delivered",
"Direction": "internal"
},
{
"Id": "Message03",
"Status": "read",
"Direction": "inbound"
},
{
"Id": "Message04",
"Status": "read",
"Direction": "outbound"
}
]
},
{
"Id": "Conversation02",
"Messages": [
{
"Id": "Message05",
"Status": "read",
"Direction": "outbound"
}
]
}
]
},
{
"Id": "Session02",
"Conversations": [
{
"Id": "Conversation03",
"Messages": [
{
"Id": "Message06",
"Status": "read",
"Direction": "inbound"
},
{
"Id": "Message07",
"Status": "delivered",
"Direction": "internal"
}
]
},
{
"Id": "Conversation04",
"Messages": []
}
]
}
]
},
{
"Id": "OP02",
"Sessions": [
{
"Id": "Session03",
"Conversations": []
}
]
},
{
"Id": "OP03",
"Sessions": []
}
]
如何通过 aggregate 和 update_one 查询完成这些结果?
How can I accomplish these results with an aggregate and update_one queries?
以下是这两个查询的直观说明:
Here comes a visual explanation of both queries:
推荐答案
我已经编写了聚合查询
db.session.aggregate([
{
$unwind:"$Sessions"
},
{
$unwind:"$Sessions.Conversations"
},
{
$unwind:"$Sessions.Conversations.Messages"
},
{
$match:{
"Sessions.Id" : "Session01",
"Sessions.Conversations.Messages.Direction":{
$in:[
"inbound", "outbound"
]
},
"Sessions.Conversations.Messages.Status":{
$in:[
"sent", "delivered"
]
}
}
},
{
$group:{
"_id":"$Sessions.Conversations.Id",
"Messages":{
$push:"$Sessions.Conversations.Messages"
}
}
}
]).pretty()
输出
{
"_id" : "Conversation02",
"Messages" : [
{
"Id" : "Message05",
"Status" : "sent",
"Direction" : "outbound"
}
]
}
{
"_id" : "Conversation01",
"Messages" : [
{
"Id" : "Message03",
"Status" : "delivered",
"Direction" : "inbound"
},
{
"Id" : "Message04",
"Status" : "sent",
"Direction" : "outbound"
}
]
}
现在要更新文档:
我使用了位置过滤器
db.session.update(
{},
{
$set:{
"Sessions.$[session].Conversations.$[].Messages.$[message].Status":"read"
}
},
{
"arrayFilters": [{"session.Id":"Session01"},{ "message.Id": "Message05" }]
}
)
这会将"session.Id":"Session01"和"message.Id": "Message05"
希望这会对您有所帮助. :)
Hope this will help you. :)
更新
db.session.update(
{},
{
$set:{
"Sessions.$[session].Conversations.$[].Messages.$[message].Status":"read"
}
},
{
"arrayFilters": [
{
"session.Id":"Session01"
},
{
"message.Direction": {
$in :[
"inbound",
"outbound"
]
},
"message.Status": {
$in :[
"sent",
"delivered"
]
}
}
]
}
)
这篇关于使用基于多级对象值的条件过滤嵌套数组并更新它们-MongoDB聚合+更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
