Mongoose聚合:基于Model.associated_Model.associated _Model.field的

Question

我有三个型号User、Profile和Institution。

用户

const UserSchema = new Schema({
name: {
type: Schema.Types.String,
},
profile: {
type: Schema.Types.ObjectId,
ref: "profiles",
},
});

配置文件

const ProfileSchema = new Schema({
institution: {
type: Schema.Types.ObjectId,
ref: "institutions",
},
});

机构

const InstitutionSchema = new Schema({
name: {
type: String,
required: true,
},
});

我正在尝试获取用户列表WHERE机构=机构名称：

User.aggregate([
{
$lookup: {
from: "profiles",
let: { profiles_id: "$profile" },
pipeline: [
{
$lookup: {
from: "institutions",
pipeline: [
{
$match: { name: institution_name },
},
],
as: "institution",
},
},
{ $unwind: "$institution" },
],
as: "profile",
},
},
{ $unwind: "$profile" },
{
$project: {
name: "$name",
institution: "$profile.institution.name",
},
},
]);

出于某种奇怪的原因，这是返回所有用户的列表，但它将机构字段替换为我使用的institution_name过滤值。知道怎么解决这个问题吗？

Answer 1

查询中的主要错误是没有根据某些条件执行联接。在具有profiles集合的第一个$lookup中，您将profile作为profiles_id传递，但不在管道中使用它。与具有CCD_ 11的第二CCD_ 10的情况相同。

试试这个：

const institution_name = "Institute 1";
db.users.aggregate([
{
$lookup: {
from: "profiles",
let: { profiles_id: "$profile" },
pipeline: [
{
$match: {
// Join condition.
$expr: { $eq: ["$_id", "$$profiles_id"] }
}
},
{
$lookup: {
from: "institutions",
let: { institution_id: "$institution" },
pipeline: [
{
$match: {
name: institution_name,
// Join condition.
$expr: { $eq: ["$_id", "$$institution_id"] }
}
}
],
as: "institution"
}
},
{ $unwind: "$institution" },
],
as: "profile",
}
},
{ $unwind: "$profile" },
{
$project: {
name: "$name",
institution: "$profile.institution.name"
}
}
]);

输出：

/* 1 createdAt:3/13/2021, 6:19:07 PM*/
{
"_id" : ObjectId("604cb4c36b2dcb17e8b152b8"),
"name" : "Dheemanth Bhat",
"institution" : "Institute 1"
},
/* 2 createdAt:3/13/2021, 6:19:07 PM*/
{
"_id" : ObjectId("604cb4c36b2dcb17e8b152b9"),
"name" : "Ahmed Ghrib",
"institution" : "Institute 1"
}

测试数据：

users集合：

/* 1 createdAt:3/13/2021, 6:19:07 PM*/
{
"_id" : ObjectId("604cb4c36b2dcb17e8b152b8"),
"name" : "Dheemanth Bhat",
"profile" : ObjectId("604cb4b16b2dcb17e8b152b5")
},
/* 2 createdAt:3/13/2021, 6:19:07 PM*/
{
"_id" : ObjectId("604cb4c36b2dcb17e8b152b9"),
"name" : "Ahmed Ghrib",
"profile" : ObjectId("604cb4b16b2dcb17e8b152b6")
},
/* 3 createdAt:3/13/2021, 6:19:07 PM*/
{
"_id" : ObjectId("604cb4c36b2dcb17e8b152ba"),
"name" : "Alex Rider",
"profile" : ObjectId("604cb4b16b2dcb17e8b152b7")
}

profiles集合：

/* 1 createdAt:3/13/2021, 6:18:49 PM*/
{
"_id" : ObjectId("604cb4b16b2dcb17e8b152b5"),
"institution" : ObjectId("604cb49a6b2dcb17e8b152b2")
},
/* 2 createdAt:3/13/2021, 6:18:49 PM*/
{
"_id" : ObjectId("604cb4b16b2dcb17e8b152b6"),
"institution" : ObjectId("604cb49a6b2dcb17e8b152b2")
},
/* 3 createdAt:3/13/2021, 6:18:49 PM*/
{
"_id" : ObjectId("604cb4b16b2dcb17e8b152b7"),
"institution" : ObjectId("604cb49a6b2dcb17e8b152b3")
},
/* 4 createdAt:3/13/2021, 6:18:49 PM*/
{
"_id" : ObjectId("604cb4b16b2dcb17e8b152b8"),
"institution" : ObjectId("604cb49a6b2dcb17e8b152b4")
}

institutions集合：

/* 1 createdAt:3/13/2021, 6:18:26 PM*/
{
"_id" : ObjectId("604cb49a6b2dcb17e8b152b2"),
"name" : "Institute 1"
},
/* 2 createdAt:3/13/2021, 6:18:26 PM*/
{
"_id" : ObjectId("604cb49a6b2dcb17e8b152b3"),
"name" : "Institute 2"
},
/* 3 createdAt:3/13/2021, 6:18:26 PM*/
{
"_id" : ObjectId("604cb49a6b2dcb17e8b152b4"),
"name" : "Institute 3"
}