Mongo多层聚合查找组 [英] Mongo Multiple Level Aggregate Lookup Group
本文介绍了Mongo多层聚合查找组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有3个馆藏:用户,药房和城市.我希望我的结果看起来像这样:
{_ID: ,电子邮件: ,出生日期: ,类型: ,药房:{_ID: ,日程: ,名称: ,地址: ,电话: ,用户:,城市: {名称:,},},}
但是,我要在第一层拿出城市物体,我想把它作为药房集合的子代.
这是我当前正在使用的管道:
User.aggregate([{$ match:{"_id":id}},{$ lookup:{来自:Dispensary.collection.name,localField:药房",foreignField:"_ id",如:药房"},},{"$ unwind":{路径:"$ dispensary",preserveNullAndEmptyArrays:true},},{$ lookup:{来自:City.collection.name,localField:"dispensary.city",foreignField:"_ id",如:城市"},},{"$ unwind":{path:"$ city",preserveNullAndEmptyArrays:true}},{"$ group":{_id:"$ _ id",电子邮件:{$ first:'$ email'},生日:{$ first:'$ birthdate'},类型:{$ first:'$ type'},药房:{$ push:"$ dispensary"},城市:{$ push:"$ city"},},},{"$ unwind":{path:"$ dispensary",preserveNullAndEmptyArrays:true}},{"$ unwind":{path:"$ city",preserveNullAndEmptyArrays:true}},],(aggErr,aggResult)=>{(aggErr)?console.log(aggResult):console.log(aggResult)})
模式:
const CitySchema = new Schema({名称:{类型:字符串,必填:true,唯一:true},zip:{类型:字符串,必填:true},});const DispensarySchema = new Schema({名称:{类型:字符串,必填:true},地址:{类型:字符串,必填:true},经度:{类型:字符串,必填:true},纬度:{类型:字符串,必填:true},电话:{类型:字符串,必填:true},用户:{type:mongoose.Schema.Types.ObjectId,参考:'User'},时间表:[{type:mongoose.Schema.Types.ObjectId,参考:'Schedule'}],城市:{type:mongoose.Schema.Types.ObjectId,编号:"City"},})const UserSchema = new Schema({名称:{类型:字符串,必填:true},电子邮件:{类型:字符串,必填:true,唯一性:true},密码:{type:String,必填:true},生日:{类型:日期,必填:true},类型:{类型:字符串,枚举:['ADMIN','DISPENSARY','CUSTOMER'],必填:true},VerificationToken:{类型:字符串,必填:false},resetPasswordToken:{类型:字符串,必填:false},resetPasswordExpires:{类型:字符串,必填:false},isVerified:{类型:布尔值,必填:true},isActive:{类型:布尔值,必填:true},last_session:{类型:日期},last_ip_session:{类型:字符串},药房:{type:mongoose.Schema.Types.ObjectId,编号:"Dispensary"},},{时间戳记:true})
解决方案
您可以通过 pipeline
使用另一个 lookup
方法,这可以使您创建更多的条件/子-查询功能内部的查询.请参阅参考资料:汇总查找
User.aggregate([{$ match:{"_id":id}},{$ lookup:{来自:Dispensary.collection.name,让:{dispensaryId:"$ dispensary"},管道:[{$ match:{$ expr:{$ eq:["$ _id","$$ dispensaryId"]}}},{$ lookup:{来自:City.collection.name,localField:城市",foreignField:"_ id",如:城市"},},{$ unwind:{路径:"$ city",reserveNullAndEmptyArrays:正确}}]如:药房",},},{$ unwind:{路径:"$ dispensary",reserveNullAndEmptyArrays:正确}},{"$ group":{_ID: : {_id:"$ _ id",电子邮件:"$ email",生日:'$ birthdate',类型:"$ type"药房:"$ dispensary"}}}],(aggErr,aggResult)=>{(aggErr)?console.log(aggResult):console.log(aggResult)})
更新:管道注:要在管道阶段引用变量,请使用"$$"语法.
I have 3 collections, User, Dispensary and City. I want my result to look like this:
{
_id: ,
email: ,
birthdate: ,
type: ,
dispensary: {
_id: ,
schedule: ,
name: ,
address: ,
phone: ,
user:,
city: {
name:,
},
},
}
However I am getting the city object out, in the first level, and I want to get it as child of the dispensary collection.
This is my current pipeline I'm using:
User.aggregate
([
{
$match: { "_id": id }
},
{
$lookup:
{
from: Dispensary.collection.name,
localField: "dispensary",
foreignField: "_id",
as: "dispensary"
},
},
{"$unwind": {path:"$dispensary",preserveNullAndEmptyArrays: true} ,},
{
$lookup:
{
from: City.collection.name,
localField: "dispensary.city",
foreignField: "_id",
as: "city"
},
},
{"$unwind": {path:"$city",preserveNullAndEmptyArrays: true}} ,
{
"$group": {
_id: "$_id",
email : { $first: '$email' },
birthdate : { $first: '$birthdate' },
type : { $first: '$type' },
dispensary: { $push: "$dispensary" },
city: { $push: "$city" },
},
},
{"$unwind": {path:"$dispensary",preserveNullAndEmptyArrays: true}} ,
{"$unwind": {path:"$city",preserveNullAndEmptyArrays: true}} ,
], (aggErr, aggResult) => {
(aggErr) ? console.log(aggResult)
: console.log(aggResult)
})
SCHEMAS:
const CitySchema = new Schema({
name: { type: String, required: true, unique:true },
zip: { type: String, required: true },
});
const DispensarySchema = new Schema({
name: { type: String, required: true },
address: { type: String, required: true },
longitude: { type: String, required: true },
latitude: { type: String, required: true },
phone: { type: String, required: true },
user: {type: mongoose.Schema.Types.ObjectId, ref: 'User'},
schedule: [{type: mongoose.Schema.Types.ObjectId, ref: 'Schedule'}],
city: {type: mongoose.Schema.Types.ObjectId, ref: 'City'},
})
const UserSchema = new Schema({
name: { type: String, required: true },
email: { type: String, required: true, unique: true },
password: { type:String, required: true },
birthdate: { type: Date, required: true },
type: { type: String, enum: ['ADMIN','DISPENSARY','CUSTOMER'], required: true},
verificationToken: { type: String, required: false },
resetPasswordToken: { type: String, required: false },
resetPasswordExpires: { type: String, required: false },
isVerified: { type: Boolean, required: true },
isActive: { type: Boolean, required: true },
last_session: { type: Date },
last_ip_session: { type:String },
dispensary: {type: mongoose.Schema.Types.ObjectId, ref: 'Dispensary'},
},
{ timestamps: true }
)
解决方案
You can used another lookup
method using pipeline
, This allow you to make more condition/sub-query inside of the lookup function. see reference: aggregate-lookup
User.aggregate([
{
$match: { "_id": id }
},
{
$lookup: {
from: Dispensary.collection.name,
let: {dispensaryId: "$dispensary"},
pipeline: [
{
$match: {
$expr: {
$eq: ["$_id", "$$dispensaryId"]
}
}
},
{
$lookup:
{
from: City.collection.name,
localField: "city",
foreignField: "_id",
as: "city"
},
},
{
$unwind: {
path:"$city",
preserveNullAndEmptyArrays: true
}
}
]
as: "dispensary",
},
},
{
$unwind: {
path:"$dispensary",
preserveNullAndEmptyArrays: true
}
},
{
"$group": {
_id: : {
_id: "$_id",
email : '$email' ,
birthdate : '$birthdate' ,
type : '$type'
dispensary: "$dispensary"
}
}
}
], (aggErr, aggResult) => {
(aggErr) ? console.log(aggResult)
: console.log(aggResult)
})
Update: Pipeline NOTE: To reference variables in pipeline stages, use the "$$" syntax.`
这篇关于Mongo多层聚合查找组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文