使用$ group和$ push进入子文档的MongoDb聚合查询 [英] MongoDb aggregation query with $group and $push into subdocument
问题描述
我对MongoDb聚合的$group参数有疑问.我的数据结构如下:
I have a question regarding the $group argument of MongoDb aggregations. My data structure looks as follows:
我的事件" 集合包含以下单个文档:
My "Event" collection contains this single document:
{
"_id": ObjectId("mongodbobjectid..."),
"name": "Some Event",
"attendeeContainer": {
"min": 0,
"max": 10,
"attendees": [
{
"type": 1,
"status": 2,
"contact": ObjectId("mongodbobjectidHEX1")
},
{
"type": 7,
"status": 4,
"contact": ObjectId("mongodbobjectidHEX2")
}
]
}
}
我的联系人" 集合包含以下文档:
My "Contact" collection contains these documents:
{
"_id": ObjectId("mongodbobjectidHEX1"),
"name": "John Doe",
"age": 35
},
{
"_id": ObjectId("mongodbobjectidHEX2"),
"name": "Peter Pan",
"age": 60
}
我想做的是对事件" 集合执行aggregate查询，并获得带有完整联系人"数据的以下结果:
What I want to do is perform an aggregate query on the "Event" collection and get the following result with full "contact" data:
{
"_id": ObjectId("mongodbobjectid..."),
"name": "Some Event",
"attendeeContainer": {
"min": 0,
"max": 10,
"attendees": [
{
"type": 1,
"status": 2,
"contact": {
"_id": ObjectId("mongodbobjectidHEX1"),
"name": "John Doe",
"age": 35
}
},
{
"type": 7,
"status": 4,
"contact": {
"_id": ObjectId("mongodbobjectidHEX2"),
"name": "Peter Pan",
"age": 60
}
}
]
}
}
我现在正在使用的参数如下所示(简化版):
The arguments I am using right now look as follows (shortened version):
"$unwind" : "$attendeeContainer.attendees",
"$lookup" : { "from" : "contactinfo", "localField" : "attendeeContainer.attendees.contact","foreignField" : "_id", "as" : "contactInfo" },
"$unwind" : "$contactInfo",
"$group" : { "_id": "$_id",
"name": { "$first" : "$name" },
...
"contact": { "$push": { "contact": "$contactInfo"} }
}
但是，这导致联系人"数组处于事件"级别(由于分组)，而不是该数组的一个文档位于每个"attendeeContainer.attendees"处. 如何将联系人"数组推入"attendeeContainer.attendees" ? (如上面所需的输出所示)
However, this leads to the "contact" array being on "Event" level (because of the grouping) instead of one document of the array being at each "attendeeContainer.attendees". How can I push the "contact" array to be at "attendeeContainer.attendees"? (as shown in the desired output above)
我尝试过类似的事情:
"attendeeContainer.attendees.contact": { "$push": { "contact": "$contactInfo"} }
但是mongodb显然不允许使用."在$group阶段.
But mongodb apparently does not allow "." at $group stage.
推荐答案
Try running the following aggregation pipeline, the key is using a final $project pipeline to create the attendeeContainer subdocument:
db.event.aggregate([
{ "$unwind": "$attendeeContainer.attendees" },
{
"$lookup" : {
"from" : "contactinfo",
"localField" : "attendeeContainer.attendees.contact",
"foreignField" : "_id",
"as" : "attendeeContainer.attendees.contactInfo"
}
},
{ "$unwind": "$attendeeContainer.attendees.contactInfo" },
{
"$group": {
"_id" : "$_id",
"name": { "$first": "$name" },
"min" : { "$first": "$attendeeContainer.min" },
"max" : { "$first": "$attendeeContainer.max" },
"attendees": { "$push": "$attendeeContainer.attendees" }
}
},
{
"$project": {
"name": 1,
"attendeeContainer.min": "$min",
"attendeeContainer.max": "$min",
"attendeeContainer.attendees": "$attendees"
}
}
])
调试技巧
在第4阶段调试管道，您将获得结果
Debugging the pipeline at the 4th stage, you would get the result
db.event.aggregate([
{ "$unwind": "$attendeeContainer.attendees" },
{
"$lookup" : {
"from" : "contactinfo",
"localField" : "attendeeContainer.attendees.contact",
"foreignField" : "_id",
"as" : "attendeeContainer.attendees.contactInfo"
}
},
{ "$unwind": "$attendeeContainer.attendees.contactInfo" },
{
"$group": {
"_id": "$_id",
"name": { "$first": "$name" },
"min" : { "$first": "$attendeeContainer.min" },
"max" : { "$first": "$attendeeContainer.max" },
"attendees": { "$push": "$attendeeContainer.attendees" }
}
}/*,
{
"$project": {
"name": 1,
"attendeeContainer.min": "$min",
"attendeeContainer.max": "$min",
"attendeeContainer.attendees": "$attendees"
}
}*/
])
管道结果
{
"_id" : ObjectId("582c789282a9183adc0b53f5"),
"name" : "Some Event",
"min" : 0,
"max" : 10,
"attendees" : [
{
"type" : 1,
"status" : 2,
"contact" : ObjectId("582c787682a9183adc0b53f3"),
"contactInfo" : {
"_id" : ObjectId("582c787682a9183adc0b53f3"),
"name" : "John Doe",
"age" : 35
}
},
{
"type" : 7,
"status" : 4,
"contact" : ObjectId("582c787682a9183adc0b53f4"),
"contactInfo" : {
"_id" : ObjectId("582c787682a9183adc0b53f4"),
"name" : "Peter Pan",
"age" : 60
}
}
]
}
和最后的 $project 管道将为您提供所需的结果:
and the final $project pipeline will give you the desired result:
db.event.aggregate([
{ "$unwind": "$attendeeContainer.attendees" },
{
"$lookup" : {
"from" : "contactinfo",
"localField" : "attendeeContainer.attendees.contact",
"foreignField" : "_id",
"as" : "attendeeContainer.attendees.contactInfo"
}
},
{ "$unwind": "$attendeeContainer.attendees.contactInfo" },
{
"$group": {
"_id": "$_id",
"name": { "$first": "$name" },
"min" : { "$first": "$attendeeContainer.min" },
"max" : { "$first": "$attendeeContainer.max" },
"attendees": { "$push": "$attendeeContainer.attendees" }
}
},
{
"$project": {
"name": 1,
"attendeeContainer.min": "$min",
"attendeeContainer.max": "$min",
"attendeeContainer.attendees": "$attendees"
}
}/**/
])
所需/实际输出
{
"_id" : ObjectId("582c789282a9183adc0b53f5"),
"name" : "Some Event",
"attendeeContainer" : {
"min" : 0,
"max" : 10,
"attendees" : [
{
"type" : 1,
"status" : 2,
"contact" : ObjectId("582c787682a9183adc0b53f3"),
"contactInfo" : {
"_id" : ObjectId("582c787682a9183adc0b53f3"),
"name" : "John Doe",
"age" : 35
}
},
{
"type" : 7,
"status" : 4,
"contact" : ObjectId("582c787682a9183adc0b53f4"),
"contactInfo" : {
"_id" : ObjectId("582c787682a9183adc0b53f4"),
"name" : "Peter Pan",
"age" : 60
}
}
]
}
}
这篇关于使用$ group和$ push进入子文档的MongoDb聚合查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
