Mongodb-通过多个集合聚合特定的结果格式

Question

我需要使用来自多个集合的信息创建一个聚合，并以特定的结果格式排列它们。这里我们有三个集合(父集合、任务集合、插槽集合；这些集合的模型不能再更改了(。每个父级可以有许多任务，这些任务由不同的插槽组成(请参阅下面的简化示例数据(

插槽集合

[
{
_id: ObjectId('6015720974cff84db3499cad'),
name: 'Test 1',
start: ISO-Date('2021-07-13T09:00:00.000+00:00'),
end: ISO-Date('2021-07-13T09:15:00.000+00:00'),
parentId: 1,
taskId: 1
},
{
_id: ObjectId('6015720974cff84db3478cad'),
name: 'Test 2',
start: ISO-Date('2021-07-13T09:15:00.000+00:00'),
end: ISO-Date('2021-07-13T09::30.000+00:00'),
parentId: 1,
taskId: 1
},
{
_id: ObjectId('6015720974cff84db3499bcd'),
name: 'Test 3',
start: ISO-Date('2021-07-13T11:00:00.000+00:00'),
end: ISO-Date('2021-07-13T11:15:00.000+00:00'),
parentId: 1,
taskId: 2
},
{
_id: ObjectId('6015720974cff84db3499efg'),
name: 'Test 4',
start: ISO-Date('2021-07-13T13:00:00.000+00:00'),
end: ISO-Date('2021-07-13T13:15:00.000+00:00'),
parentId: 2,
taskId: 3
},
{
_id: ObjectId('6015720974cff84db349967e'),
name: 'Test 5',
start: ISO-Date('2021-07-13T13:15:00.000+00:00'),
end: ISO-Date('2021-07-13T13:30:00.000+00:00'),
parentId: 2,
taskId: 3
},
]

任务集合

[
{
_id: ObjectId('6015720974cff84db3499c87'),
name: 'Task 1',
taskId: 1,
},
{
_id: ObjectId('6015720974cff84db3499b6b'),
name: 'Task 2',
taskId: 2,
},
{
_id: ObjectId('6015720974cff84db3499b19'),
name: 'Task 3',
taskId: 3,
}
]

父集合

[
{
_id: ObjectId('6015720974cff84db34995a6'),
name: 'Parent 1',
parentId: 1
},
{
_id: ObjectId('6015720974cff84db349962f'),
name: 'Parent 2',
parentId: 2
}
]

结果应具有以下格式：

[
{
parentName: 'Parent 1',
tasks: [
{
taskName: 'Task 1',
slots: [
{ 
name: 'Test 1',
start: ISO-Date('2021-07-13T09:00:00.000+00:00'),
end: ISO-Date('2021-07-13T09:15:00.000+00:00'),
},
{ 
name: 'Test 2',
start: ISO-Date('2021-07-13T09:15:00.000+00:00'),
end: ISO-Date('2021-07-13T09::30.000+00:00'),
},
]
},
{
taskName: 'Task 2',
slots: [
{ 
name: 'Test 3',
start: ISO-Date('2021-07-13T11:00:00.000+00:00'),
end: ISO-Date('2021-07-13T11:15:00.000+00:00'),
}
]
}
]
},
{
parentName: 'Parent 2',
tasks: [
{
taskName: 'Task 3',
slots: [
{ 
name: 'Test 4',
start: ISO-Date('2021-07-13T13:00:00.000+00:00'),
end: ISO-Date('2021-07-13T13:15:00.000+00:00'),
},
{ 
name: 'Test 5',
start: ISO-Date('2021-07-13T13:15:00.000+00:00'),
end: ISO-Date('2021-07-13T13:30:00.000+00:00'),
}
]
}
]
}
]

如何通过给定的数据实现这种结果结构？查找部分不是什么大事，但我目前很难实现这种结构，因为我对MongoDB聚合框架还很陌生。非常感谢你的提示。

Answer 1

您可以使用查找来加入集合

• $lookup加入集合
• $unwind解构数组
• $group重建阵列

这是代码，

db.Task.aggregate([
{
"$lookup": {
"from": "Slot",
"localField": "taskId",
"foreignField": "taskId",
"as": "slots"
}
},
{ "$unwind": "$slots" },
{
"$lookup": {
"from": "Parent",
"localField": "slots.parentId",
"foreignField": "parentId",
"as": "parents"
}
},
{ "$unwind": "$parents" },
{
$group: {
_id: { pId: "$parents._id", tId: "$taskId" },
parentName: { $first: "$parents.name" },
taskName: { "$first": "$name" },
slots: { $push: "$slots" }
}
},
{
$group: {
_id: "$_id.pId",
parentName: { $first: "$parentName" },
tasks: {
$push: {
taskName: "$taskName",
slots: "$slots"
}
}
}
}
])

正在工作的Mongo游乐场