Mongodb汇总三个集合 [英] Mongodb aggregate three collections
问题描述
过去两天学习MongoDB,我正在尝试汇总三个集合,但无法实现
Learning MongoDB for the past two days and I am trying to aggregate three collections but unable to achieve it
下面是数据库中维护的四个集合
Below are the four collection maintaining in the database
大学
{
"_id" : "5834ecf7432d92675bde9d82",
"name": "NIFT"
}
大学
{
"_id" : "5834ecf7432d92675bde9d83",
"name": "NIFT Hyderabad",
"university_id":"5834ecf7432d92675bde9d82"
}
部门
{
"_id" : "5834ecf7432d92675bde9d84",
"department_name": "Fashion Technology",
"college_id" : "5834ecf7432d92675bde9d83"
},
{
"_id" : "5834ecf7432d92675bde9d85",
"department_name": "Merchandising",
"college_id" : "5834ecf7432d92675bde9d83"
}
部分
{
"_id" : "5834ecf7432d92675bde9d86",
"section_name": "A",
"students" : "56",
"department_id":"5834ecf7432d92675bde9d84"
},
{
"_id" : "5834ecf7432d92675bde9d87",
"section_name": "B",
"students" : "60",
"department_id":"5834ecf7432d92675bde9d84"
},
{
"_id" : "5834ecf7432d92675bde9d86",
"section_name": "A",
"students" : "55",
"department_id":"5834ecf7432d92675bde9d85"
},
{
"_id" : "5834ecf7432d92675bde9d87",
"section_name": "B",
"students" : "44",
"department_id":"5834ecf7432d92675bde9d85"
}
我在这里尝试以以下格式实现输出
Here I am trying to achieve the output in the below format
预期产量
[{
"_id": "5834ecf7432d92675bde9d83",
"name": "NIFT Hyderabad",
"university_id": "5834ecf7432d92675bde9d82",
"departments": [{
"_id": "5834ecf7432d92675bde9d84",
"department_name": "CSE",
"college_id": "5834ecf7432d92675bde9d83",
"sections": [{
"_id": "5834ecf7432d92675bde9d86",
"section_name": "A",
"students": "56",
"department_id": "5834ecf7432d92675bde9d84"
}, {
"_id": "5834ecf7432d92675bde9d87",
"section_name": "B",
"students": "60",
"department_id": "5834ecf7432d92675bde9d84"
}]
},
{
"_id": "5834ecf7432d92675bde9d85",
"department_name": "Mechanical",
"college_id": "5834ecf7432d92675bde9d83",
"sections": [{
"_id": "5834ecf7432d92675bde9d86",
"section_name": "A",
"students": "55",
"department_id": "5834ecf7432d92675bde9d85"
},
{
"_id": "5834ecf7432d92675bde9d87",
"section_name": "B",
"students": "44",
"department_id": "5834ecf7432d92675bde9d85"
}
]
}
]
}]
但是,我将大学的科室和科室分成不同的阵列,但无法像上述格式那样获得
But, I am getting department and sections in separate arrays for college but not able to get like in the above format
查询
db.college.aggregate([
{"$match": { "university_id": "5834ecf7432d92675bde9d82" } },
{"$lookup": {
"localField": "_id",
"from": "departments",
"foreignField": "college_id",
"as": "departments"
}},
{"$unwind":"$departments"},
{$group : {_id : "$_id", departments : {$push : "$departments" }}},
{"$lookup": {
"localField": "departments._id",
"from": "sections",
"foreignField": "department_id",
"as": "sections"}
}
])
任何人都可以帮助我解决这个问题,这对我非常有帮助.
Can any one help me to solve this issue, it will be very helpful for me.
推荐答案
您可以在下面的汇总查询中进行尝试.
You can try below aggregation query.
下面的查询在加入sections
时将sections
推入department
中,并且将$group
推入部门以创建最终结构.
The below query pushes the sections
into department
when they are joined and $group
to push department to create the final structure.
db.college.aggregate([
{
"$match": {
"university_id": "5834ecf7432d92675bde9d82"
}
},
{
"$lookup": {
"localField": "_id",
"from": "departments",
"foreignField": "college_id",
"as": "departments"
}
},
{
"$unwind": {
"path": "$departments",
"preserveNullAndEmptyArrays": true
}
},
{
"$lookup": {
"localField": "departments._id",
"from": "sections",
"foreignField": "department_id",
"as": "departments.sections"
}
},
{
"$group": {
"_id": "$_id",
"name": {
"$first": "$name"
},
"university_id": {
"$first": "$university_id"
},
"departments": {
"$push": "$departments"
}
}
}
])
这篇关于Mongodb汇总三个集合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!