mongodb - Mongoose 使用聚合/填充查询参考集合数据

问题描述

我是 MongoDB 和 Express JS 的新手

我有一个产品模型看起来像

const productSchema = mongoose.Schema({
   name: String,
   title: String,
   brand_id: { type: mongoose.Schema.Types.ObjectId, ref: 'brand' },
   varient:[{
       country_id: { type: mongoose.Schema.Types.ObjectId,ref: 'countries'},
       max_retail_price : Number,
       profit_margin : Number
   }],

和订单模型

 const orderTransactionSchema = mongoose.Schema({
       shop_id: { type: mongoose.Schema.Types.ObjectId, ref: 'shop' },
       brand_id:{ type: mongoose.Schema.Types.ObjectId, ref: 'brand' },
       product_id: { type: mongoose.Schema.Types.ObjectId, ref: 'product' },
       product_varient_id:{ type: mongoose.Schema.Types.ObjectId, ref: 'product.varient._id' },
       transaction_id:{ type: mongoose.Schema.Types.ObjectId, ref: 'transaction' }
})
module.exports = mongoose.model('order', orderTransactionSchema );

在我的产品集合中，每个产品都可以有多个变体。但是在订单收集中，用户只能在一个产品下订购一个产品变体。

我正在尝试显示具有特定产品详细信息和变体详细信息的订单，但问题是当我尝试使用填充/聚合来显示它时，我得到了响应数组中的所有变体。实际上，我只想要订单集合中的一种产品和变体详细信息。

这是我尝试过的

order.aggregate([{ $match :{} },
{
   $lookup: {
     from: "products",
     localField: "product_id",
     foreignField: "_id",
     as: "product_data"
   }
},
 ]).exec(function(err,result){
    console.log(result);
 });

我得到的输出为

{ _id: 5c8a010b8feeb875abc1b066,
   shop_id: 5c7d194ca10ea45c0c03a0ee,
   brand_id: 5c41a8c34272c61a176b7639,
   product_varient_id: 5c41a9f3f8e1e71aa75b4f32,
   transaction_id: 5c6670d5b6c63d0762c6cc77,
   product_id: 5c41aac4d45a731af564c433,
   product_data:
    [ { _id: 5c41aac4d45a731af564c433,
        brand_id: 5c41a8c34272c61a176b7639,
        image: 'test.jpg',
        varient:        //getting all the varients here
         [ { _id: 5c4ee531bc20b27948b3aa98,
             sell_rate_local: 66,
             country_id: 5c01149d3c20440a6b2e4928 },
           { _id: 5c4ee53bbc20b27948b3aa99,
             sell_rate_local: 66,
             country_id: 5c00e1697dd7a23f08bdae68 } ],
        __v: 0 } ] } ]

在 Order 表中，有 porduct_id 和 product_varient_id 我只想用 product_varient_id 填充产品。

我还尝试了 Populate

order.find().
             populate([

                  { path: 'shop_id', model: 'shop',select : 'name accounts' },    //it works
                 { path: 'transaction_id', model: 'transaction' }, //it wrks                     
                { path: 'product_varient_id', model: 'product', select:'product.varient.name'},
                 ]).then(result =>
                 {
                    // console.log(result);
                 }).catch(err =>{

                //     console.log(err);
                 });

这些是样品产品和订单文件

订单文件样本：

{  
   "_id":"5c77a025d65a892f6acf1803",
   "shop_id":"5c7d194ca10ea45c0c03a0ee",
   "brand_id":"5c41a8b44272c61a176b7638",
   "product_varient_id":"5c41a9f3f8e1e71aa75b4f32",
   "buy_rate":10,
   "buy_rate_after_discount":20,
   "product_mrp":30,
   "sell_rate":40,
   "customer_mobile":123456789,
   "status":true,
   "transaction_id":"5c6670c9b6c63d0762c6cc76",
   "product_id":"5c41a95ff8e1e71aa75b4f30",
   "createdAt":"2019-02-28T08:47:33.097Z",
   "updatedAt":"2019-02-28T08:47:33.097Z",
   "__v":0
}

产品文件样本：

{  
   "_id":"5c41aac4d45a731af564c433",
   "recharge_type":[  
      "5c00d9cf7dd7a23f08bdae5e"
   ],
   "name":"25 OC - Product 1",
   "title":"First installation recharge",
   "description":"0.1 credit for first time installation",
   "offer_message":"Hi.. You got 0.1 credits..!!",
   "brand_id":"5c41a8c34272c61a176b7639",
   "buy_rate":20,
   "profit_margin":80,
   "image":"test.jpg",
   "varient":[  
      {  
         "_id":"5c4ee531bc20b27948b3aa98",
         "display_name":"testlia",
         "profit_margin":66,
         "max_retail_price":66,
         "sell_rate":66,
         "sell_rate_local":66,
         "country_id":"5c01149d3c20440a6b2e4928"
      },
      {  
         "_id":"5c4ee53bbc20b27948b3aa99",
         "display_name":"testrinu",
         "profit_margin":66,
         "max_retail_price":66,
         "sell_rate":66,
         "sell_rate_local":66,
         "country_id":"5c00e1697dd7a23f08bdae68"
      }
   ],
   "createdAt":"2019-01-18T10:30:28.991Z",
   "updatedAt":"2019-01-28T11:19:23.662Z",
   "__v":0
}

标签： mongodbexpressmongodb-query

db.orders.aggregate([{ $lookup: { from: "products", let: { "productId": "$product_id", "productVarientId": "$product_varient_id" }, pipeline: [ { $match: { $expr: { $eq: [ "$_id", "$$productId" ]} } }, { $addFields: { varient: { $filter: { input: "$varient", as: "varient", cond: { $eq: [ "$$productVarientId", "$$varient._id" ] } } } } } ], as: "product_data" } }])

mongodb - Mongoose 使用聚合/填充查询参考集合数据

问题描述

解决方案

推荐阅读