javascript - 尝试使用聚合从 Mongodb 获取数据

Question

我有“Offers”和“Requests”集合，我需要获取用户提出的所有报价，按请求对它们进行分组，并找到每个请求的最低“Offer.price”，每个报价都有 requestId 字段。我正在使用聚合来解决这个问题，

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

这就是我得到的:

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

现在我想用“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,
  }
});

做这样的事情最好的方法是什么？感谢您的帮助!

Answer 1

考虑以下数据集:

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})

您可以使用$reduce

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

在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()

输出:

[
  {
    "_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
  }
]