mongodb aggregate多表联查多阶数组嵌套查询实现
多个表的关系如下：
比如某市的中学信息数据库
聚合查询
方法一
思路：
1.$unwind 拆解 Array
- $group 根据_id 重新组合成Array
3.最后将主表的属性重新连接
优点：在对mongodb 不熟悉的情况下，最容易想到的方法
缺点：要进行多次的stage，相当的繁琐，容易将结构搞混
代码语言：ts复制let aggregate=[
{
$lookup:
/**
* from: The target collection.
* localField: The local join field.
* foreignField: The target join field.
* as: The name for the results.
* pipeline: Optional pipeline to run on the foreign collection.
* let: Optional variables to use in the pipeline field stages.
*/
{
from: "class",
localField: "_id",
foreignField: "schoolId",
as: "class",
},
},
{
$unwind:
/**
* path: Path to the array field.
* includeArrayIndex: Optional name for index.
* preserveNullAndEmptyArrays: Optional
* toggle to unwind null and empty values.
*/
{
path: "$class",
includeArrayIndex: "classIndex",
preserveNullAndEmptyArrays: false,
},
},
{
$addFields:
/**
* newField: The new field name.
* expression: The new field expression.
*/
{
classId: {
$toString: "$class._id",
},
},
},
{
$lookup:
/**
* from: The target collection.
* localField: The local join field.
* foreignField: The target join field.
* as: The name for the results.
* pipeline: Optional pipeline to run on the foreign collection.
* let: Optional variables to use in the pipeline field stages.
*/
{
from: "student",
localField: "classId",
foreignField: "classId",
as: "student",
},
},
{
$addFields:
/**
* newField: The new field name.
* expression: The new field expression.
*/
{
"class.students": "$student",
},
},
{
$group: {
_id: "$_id",
classes: {
$push: "$class",
},
},
},
{
$lookup:
/**
* from: The target collection.
* localField: The local join field.
* foreignField: The target join field.
* as: The name for the results.
* pipeline: Optional pipeline to run on the foreign collection.
* let: Optional variables to use in the pipeline field stages.
*/
{
from: "school",
localField: "_id",
foreignField: "_id",
as: "school",
},
},
{
$unwind:
/**
* path: Path to the array field.
* includeArrayIndex: Optional name for index.
* preserveNullAndEmptyArrays: Optional
* toggle to unwind null and empty values.
*/
{
path: "$school",
},
},
{
$project:
/**
* specifications: The fields to
* include or exclude.
*/
{
_id: 0,
id: {
$toString: "$_id",
},
classes: 1,
name: "$school.name",
},
},
]
this.schoolModel.aggregate(aggregate);方法二
思路：
1.$lookup 可以支持pipeline
- 是否可是在$lookup 嵌套$lookup 呢
3.答案是显而易见的
优点：极大的减少的代码的数量
缺点：对$lookup理解不够深，可能不会想到这么用（）
个人建议多看看mongodb的官方文档 lookup
代码语言：ts复制let aggregate=[
{
$lookup:
{
from: "class",
localField: "_id",
foreignField: "schoolId",
as: "classes",
pipeline:[
{
$lookup:{
from: "student",
localField: "classId",
foreignField: "classId",
as: "students",
}
}
]
},
]
this.schoolModel.aggregate(aggregate);哇哦！是不是简单了好多。
包括但不限于这些场景，如有其他应用场景，和疑问欢迎评论区留言
我正在参与2024腾讯技术创作特训营第五期有奖征文，快来和我瓜分大奖！
