javascript - 尝试从Mongo DB收集数据(Trying to get data from Mongo DB with aggregate)

Question

I have "Offers" and "Requests" collections, I need to get all offers that user made, group them by requests and find the lowest "Offer.price" on each request, each offer has requestId field.

(我有“要约”和“请求”集合，我需要获取用户提供的所有要约，按请求将它们分组，并在每个请求上找到最低的“要约。价格”，每个要约都有requestId字段。)

I am using aggregate to solve this,

(我正在使用聚合来解决这个问题，)

 db.Offer.aggregate([{ $match: { ownerId: mongoose.Types.ObjectId(req.params.ownerId) } }, { $group: { _id: "$requestId", price: { $min: "$price" } } } ]) 

and This is what i get :

(这就是我得到的：)

  [ { _id: 5dc47241af1406031489c65c, price: 14 }, { _id: 5dc47241af1406031489c653, price: 3 }, { _id: 5dc47241af1406031489c656, price: 5 }, { _id: 5dc8add63f73953ff408f962, price: 6 }, { _id: 5dc8add63f73953ff408f969, price: 22 }, { _id: 5dc47241af1406031489c658, price: 1 } ] 

Now I want to populate these with rest of the data from "Offer"

(现在，我要用“报价”中的其余数据填充这些数据)

 const OfferSchema = new Schema({ requestId: { type: Schema.Types.ObjectId, ref: 'Request' }, ownerId: { type: Schema.Types.ObjectId, required: true, ref: 'User' }, price: { type: Number, required: true }, createdAt: { type: Date, default: Date.now }, isBest: { type: Boolean, default: false }, isWinner: { type: Boolean, default: false, } }); 

What would be best way to do something like this?

(做这样的事情的最佳方法是什么？)

Thank you for your help!

(谢谢您的帮助！)

  ask by Konstantin Agapov translate from so

Answer 1

Consider the following dataset:

(考虑以下数据集：)

db.dum.insert({ownerId:1, requestId:'a', price:3, createdAt:3, isWinner:true})
db.dum.insert({ownerId:1, requestId:'a', price:1, createdAt:1, isWinner:false})
db.dum.insert({ownerId:1, requestId:'a', price:2, createdAt:2, isWinner:true})
db.dum.insert({ownerId:1, requestId:'b', price:4, createdAt:2, isWinner:true})
db.dum.insert({ownerId:1, requestId:'b', price:5, createdAt:1, isWinner:false})
db.dum.insert({ownerId:2, requestId:'b', price:5, createdAt:1, isWinner:false})

You could use $reduce

(您可以使用$ reduce)

Here, for a grouping id, we keep all matching documents as an array ( candidates ).

(在这里，对于分组ID，我们将所有匹配的文档保留为数组（ candidates ）。)

On the project stage, for each group we iterate through the array, and reduce it to the minimal element found (by price that is)

(在project阶段，对于每个组，我们迭代数组，并将其减少到找到的最小元素（按价格计算）)

db.dum.aggregate([{
    $match: {
      ownerId: 1
    }
  },
  {
    $group: {
      _id: "$requestId",
      candidates: { $push:'$$ROOT'}
    }
  },
  {
    $project:{
      item: {
        $reduce: {
          input: '$candidates',
          initialValue: '$candidates.0',
          in: { 
            $cond: {
              if: {
                $lt: ['$$value.price', '$$this.price']
              },
              then:'$$value',
              else:'$$this'
            }
          }
        }
      }
    }
  },
  {
    $replaceRoot:{newRoot:'$item'}
  }
]).toArray()

output:

(输出：)

[
  {
    "_id" : ObjectId("5ddcc8e0eb1f0217802fb507"),
    "ownerId" : 1,
    "requestId" : "b",
    "price" : 4,
    "createdAt" : 2,
    "isWinner" : true
  },
  {
    "_id" : ObjectId("5ddcc8e0eb1f0217802fb505"),
    "ownerId" : 1,
    "requestId" : "a",
    "price" : 1,
    "createdAt" : 1,
    "isWinner" : false
  }
]