Mongo多层聚合查找组 [英] Mongo Multiple Level Aggregate Lookup Group

问题描述

我有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屋！