Question 116690 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Mongoose updateMany based on result of previous query

I have two collections energyOffers and energyOfferLogs. When a user deactivated their account I’m looking for all the remaining active energyOffers where the entity of the user is in the assignees array, not in the declinedEntities array and the offerValidTill date is less than the current timestamp.

const [energyOffers] = await EnergyOffer.find([{ 
    'assignees.id': entityID, 
    declinedEntities: { 
        $ne: leadID 
    },
    offerValidTill: { $gt: Date.now() }
}], { session });

Based on these energyOffers I need to update the corresponding energyOfferLogs. I can find these with { entityID: entityID, 'offer.offerID': offer._id } but how can I look for all these offers in the same query?

If I loop through the energyOffers I will have to perform multiple updates while my guess is that this can be done in one updateMany. I was looking into the $lookup aggregate operator (https://www.mongodb.com/docs/v6.0/reference/operator/aggregation/lookup/) but it seems that the EnergyOffer find query is too complex to perform in this.

await EnergyOfferLog.updateMany({ ??? }, {
    $set: {
        'offer.action': 'declined',
        'offer.action_date': Math.floor(Date.now()),
        'offer.action_user': user.first_name,
        'offer.action_user_id': userID
    }
});
Tags:

2

Answers


  1. 0

    Get all offer ids from the first query, e.g.

    let ids = energyOffers.map(o => o._id)

    Use $in to match logs for all matching offers:

    await EnergyOfferLog.updateMany({ entityID: entityID, 'offer.offerID': {$in: ids} }, {
    $set: {
        'offer.action': 'declined',
        'offer.action_date': Math.floor(Date.now()),
        'offer.action_user': user.first_name,
        'offer.action_user_id': userID
    }
});
    Login or Signup to reply.
  2. 0

    If you want to do it with one query only, it is not complex. You can use $lookup with a pipeline for this:

    1. Start with your $match query on the energyOffers collection
    2. Use ‘$lookupto get the matchingenergyOfferLogs`
    3. Clean the pipeline to contain only the energyOfferLogs docs
    4. Perform the $set
    5. Use $merge to save it back to energyOfferLogs collection
    db.energyOffers.aggregate([
  {$match: {
      "assignees.id": entityID,
      declinedEntities: {$ne: leadID},
      offerValidTill: {$gt: Date.now()}
    }
  },
  {$lookup: {
      from: "energyOfferLogs",
      let: {offerId: "$_id"},
      pipeline: [
        {$match: {
            $and: [
              {entityID: entityID},
              {$expr: {$eq: ["$offer.offerID", "$$offerId"]}}
            ]
          }
        }
      ],
      as: "energyOfferLogs"
    }
  },
  {$unwind: "$energyOfferLogs"},
  {$replaceRoot: {newRoot: "$energyOfferLogs"}},
  {$set: {
      "offer.action": "declined",
      "offer.action_date": Math.floor(Date.now()),
      "offer.action_user": user.first_name,
      "offer.action_user_id": userID
    }
  },
  {$merge: {into: "$energyOfferLogs"}}
])

    See how it works on the playground example

    • Answer was updated according to a remark by @Alex_Blex
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search