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

CodePudding user response：

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

CodePudding user response：

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"
    }
  },
  {$project: {energyOfferLogs: {$first: "$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