如何在mongodb中的条件下应用$ lookup? [英] How to apply $lookup with conditions in mongodb?

问题描述

我正在使用 mongodb 数据库，并希望在2个 collections 上应用 $ lookup ，但要满足特定条件.我有一个这样的名为 Company 的 collection

 新架构({名称:字符串，好处:字符串，BenefitDesc:字串，company_url:字符串，logoUrl:字符串，coverUrl:字符串，desc:字符串，createdAt:字符串，categoryId:{类型:Schema.Types.ObjectId，ref:'categories'}，}) 

和另一个名为 Referrallinks 的集合

 新架构({referral_link:字符串，referral_code:字符串，isLink:数字，offer_name:字符串，offer_desc:字符串，user_email:字符串，companyId:{类型:Schema.Types.ObjectId，ref:'companies'}，appendByAdmin:{类型:布尔值，默认值:true}，number_of_clicks:数字，referral_country:字符串，link_status:字符串，categoryId:{类型:Schema.Types.ObjectId，ref:'categories'}，number_of_clicks:{类型:数字，默认值:0}，createdAt:字符串，UpdatedAt:字符串，userId:{类型:Schema.Types.ObjectId，参考:用户"}}) 

现在，当我应用此 $ lookup

时

  Company.aggregate([{$ lookup:{来自:引荐链接"，localField:"_ id"，foreignField:'companyId'，如:引荐"}}]) 

所以我要像这样将所有具有 referrals 作为 array 的公司

  [{name:，benefiDesc:"，benefiDesc:，引荐:[]}，{name:，benefiDesc:"，benefiDesc:，引荐:[]}，{name:，benefiDesc:"，benefiDesc:，引荐:[]}，.....] 

但是我只希望在每个公司的 referrals 数组中具有具有 link_status 值 Approved 的引用./p>

**注意:**我也想将 category 对象与 company 一起使用，并尝试进行这种聚合，但是会产生错误

  {来自:引荐链接"，让:{company_id:"$ _id"}，管道:[{$ match:{$ expr:{$ and:[{$ eq:["$$ company_id"，"$ companyId"]}，{$ eq:["$ link_status"，已批准"]}]}}，{$ lookup:{来自:类别"，let:{"category_id":"$ categoryId"}，管道:[{$ match:{$ expr:{$ eq:["category_id"，"$ _ id"]}}}]，作为:公司"}}}]，作为:引荐"} 

我该如何实现?

解决方案

使用

要运行嵌套的 $ lookup ，您的代码应如下所示:

  Company.aggregate([{$ lookup:{来自:引荐链接"，让:{company_id:"$ _id"}，管道:[{$ match:{$ expr:{$ and:[{$ eq:["$$ company_id"，"$ companyId"]}，{$ eq:["$ link_status"，已批准"]}]}}}，{$ lookup:{来自:类别"，localField:"categoryId"，foreignField:"_ id"，作为:公司"}}]，作为:引荐"}}]) 

I am using mongodb databases and want to apply $lookup on 2 collections but with specific conditions. I have one collection named Company like this

new Schema({
    name: String,
    benefit: String,
    benefitDesc: String,
    company_url: String,
    logoUrl: String,
    coverUrl: String,
    desc: String,
    createdAt: String,
    categoryId: { type: Schema.Types.ObjectId, ref: 'categories' },
})

And another collection named Referrallinks like this

new Schema({
    referral_link: String,
    referral_code: String,
    isLink: Number,
    offer_name: String,
    offer_desc: String,
    user_email: String,
    companyId: { type: Schema.Types.ObjectId, ref: 'companies' },
    addedByAdmin: { type: Boolean, default: true },
    number_of_clicks: Number,
    referral_country: String,
    link_status: String,
    categoryId: { type: Schema.Types.ObjectId, ref: 'categories' },
    number_of_clicks: { type: Number, default: 0 },
    createdAt: String,
    updatedAt: String,
    userId: { type: Schema.Types.ObjectId, ref: 'users' }
})

Now when i apply this $lookup

Company.aggregate([{
    $lookup: {
        from: 'referrallinks',
        localField: '_id',
        foreignField: 'companyId',
        as: 'referrals'
    }
}])

So i am getting all companies with referrals as array like this

[
  {name : '', benefit : '', benefiDesc : '', referrals : []},
  {name : '', benefit : '', benefiDesc : '', referrals : []},
  {name : '', benefit : '', benefiDesc : '', referrals : []},
  .....
]

But i want to have only referrals in each company's referrals array that have link_status value Approved.

**Note : ** I also want to have category object with company and tried this aggregation but it gives error

{
  from: "referrallinks",
  let: { company_id: "$_id" },
  pipeline: [{
    $match: {
      $expr: {
       $and: [
        { $eq: [ "$$company_id", "$companyId" ] },
        { $eq: [ "$link_status", "Approved" ] }
       ]
     }
    },
  {
    $lookup : {
    from : "categories",
    let  : {"category_id" : "$categoryId"},
    pipeline : [
      {
        $match : {
          $expr : {
            $eq : ["category_id","$_id"]
          }
        }
      }
    ],
    as : "company"
   }
  }
 }],
 as: "referrals"
}

How can i achieve this?

解决方案

Use $lookup with custom pipeline to specify additional filtering condition:

Company.aggregate([{
    $lookup: {
        from: "referrallinks",
        let: { company_id: "$_id" },
        pipeline: [{
            $match: {
                $expr: {
                    $and: [
                        { $eq: [ "$$company_id", "$companyId" ] },
                        { $eq: [ "$link_status", "Approved" ] }
                    ]
                }
            }
        }],
        as: "referrals"
    }
}])

EDIT: to run nested $lookup your code should look like below:

Company.aggregate([{
    $lookup: {
        from: "referrallinks",
        let: { company_id: "$_id" },
        pipeline: [
            {
                $match: {
                    $expr: {
                        $and: [
                            { $eq: [ "$$company_id", "$companyId" ] },
                            { $eq: [ "$link_status", "Approved" ] }
                        ]
                    }
                }
            },
            {
                $lookup: {
                    from: "categories",
                    localField: "categoryId",
                    foreignField: "_id",
                    as: "company"
                }
            }
        ],
        as: "referrals"
    }
}])

这篇关于如何在mongodb中的条件下应用$ lookup?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！