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. 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!

score 1 · Accepted Answer · answered Nov 26 '19 at 09:08

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

Here, for a grouping id, we keep all matching documents as an array (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)

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

Thank you! That worked perfect, For anyone who has same issues I also needed to populate results with data from other collection, $lookup didnt work for me so I made another query in .then() to populate results, you can find more here https://stackoverflow.com/questions/16680015/how-to-use-populate-and-aggregate-in-same-statement — Konstantin Agapov, Nov 28 '19 at 20:16

Trying to get data from Mongo DB with aggregate

1 Answers1