我有两个收藏-
学生合集（学生文档样本）
{
'id': '123',
'name': 'john',
'age': 25,
'fav_colors': ['red', 'black'],
'marks_in_subjects': [
{
'marks': 90,
'subject_id': 'abc'
},
{
'marks': 92,
'subject_id': 'def'
}
]
}
学科集合（2个样本文档）
{
'id': 'abc',
'name': 'math'
},
{
'id': 'def',
'name': 'physics'
}
当我查询id：'123'的学生文档时，我希望结果输出为：
{
'id': '123',
'name': 'john',
'age': 25,
'fav_colors': ['red', 'black'],
'marks_in_subjects': [
{
'marks': 90,
'subject_id': 'abc',
'subject_name': 'math'
},
{
'marks': 92,
'subject_id': 'def',
'subject_name': 'physics'
}
]
}
现在，我阅读了MongoDB聚合管道和运算符文档，但我仍然不知道如何实现这一点。疑问仍然存在，因为我甚至不确定在mongo聚合管道的帮助下是否可能实现这一点，因为JOIN发生在学生文档中数组字段的每个元素上。
如果有人能帮上忙就太好了谢谢
$match你的条件$lookup带有主题集合$addFields从返回主题name
$group通过id重建marks_in_subjects数组，并使用$first运算符db.students.aggregate([
{ $match: { id: "123" } },
{ $unwind: "$marks_in_subjects" },
{
$lookup: {
from: "subjects",
localField: "marks_in_subjects.subject_id",
foreignField: "id",
as: "marks_in_subjects.subject_name"
}
},
{
$addFields: {
"marks_in_subjects.subject_name": {
$arrayElemAt: ["$marks_in_subjects.subject_name.name", 0]
}
}
},
{
$group: {
_id: "$id",
name: { $first: "$name" },
age: { $first: "$age" },
fav_colors: { $first: "$fav_colors" },
marks_in_subjects: { $push: "$marks_in_subjects" }
}
}
])
游乐场
没有$un转阶段的第二个选项，
$match你的条件$lookup带有主题集合$addFields从主题获取主题名称
$map迭代marks_in_subjects数组的循环$duce迭代主题数组的循环，并检查subject_id是否匹配，然后返回主题名称$mergeObject合并marks_in_subjects的当前对象和新字段subject_namedb.students.aggregate([
{ $match: { id: "123" } },
{
$lookup: {
from: "subjects",
localField: "marks_in_subjects.subject_id",
foreignField: "id",
as: "subjects"
}
},
{
$addFields: {
marks_in_subjects: {
$map: {
input: "$marks_in_subjects",
as: "m",
in: {
$mergeObjects: [
"$$m",
{
subject_name: {
$reduce: {
input: "$subjects",
initialValue: "",
in: {
$cond: [{ $eq: ["$$this.id", "$$m.subject_id"]}, "$$this.name", "$$value"]
}
}
}
}
]
}
}
}
}
},
{ $unset: "subjects" }
])
游乐场
演示-https://mongoplayground.net/p/H5fHpfWz5VH
db.Students.aggregate([
{
$unwind: "$marks_in_subjects" // break into individual documents
},
{
"$lookup": { // get subject details
"from": "Subjects",
"localField": "marks_in_subjects.subject_id",
"foreignField": "id",
"as": "subjects"
}
},
{
$set: { // set name
"marks_in_subjects.name": "subjects.0.name" // pick value from 0 index
}
},
{
$group: { // join document back by id
_id: "$_id",
marks_in_subjects: { $push: "$marks_in_subjects" }
}
}
])
