Update multiple fields based on condition in aggregation pipeline MongoDB Atlas trigger

Question

I have the following pipeline that calculate the rank (sort) according to the score when the flag update is set to true:

  const pipeline = [
    {$match: {"score": {$gt: 0}, "update": true}},
    {$setWindowFields: {sortBy: {"score": -1}, output: {"rank": {$denseRank: {}}}}},
    {$merge: {into: "ranking"}}
  ];
  
  await ranking_col.aggregate(pipeline).toArray();

What i do next is to set the rank to 0 when the update flag is set to false:

ranking_col.updateMany({"update": false}, {$set: {"rank": parseInt(0, 10)}});

One of my document looks like this :

{
  "_id": "7dqe1kcA7R1YGjdwHsAkV83",
  "score": 294,
  "update": false,
  "rank": 0,
}

I want to avoid the extra updateMany call and do the equivalent inside the pipeline. MongoDB support back then told me to use the $addFields flag this way :

const pipeline = [
    {$match: {"score": {$gt: 0}, "update": true}},
    {$setWindowFields: {sortBy: {"score": -1}, output: {"rank": {$denseRank: {}}}}},
    {$addFields: {rank: {$cond: [{$eq: ['$update', false]},parseInt(0, 10),'$rank']}}},
    {$merge: {into: "ranking"}}
  ];

This is not working in my Atlas Trigger. Can you please correct my syntax or tell me a good way to do so ?

You should remove `update: true` from your match stage `{$match: {"score": {$gt: 0}, "update": true}}` — Charchit Kapoor, Aug 22 '22 at 10:14
I understand why you say so. But then, the `$setWindowField` would apply on all documents on my collection where `score > 0` which is not what i want because some ranks should not be updated... Is there a way to set the `$addFields` before the `$match` ? Or it doesn't make sense ? — Tom3652, Aug 22 '22 at 12:15
If you want the `"$denseRank"` for only `"score": {"$gt": 0}`, isn't there a possibility that `"rank"` may be non-unique in the updated collection? What do you want to happen when `"score": {"$lte": 0}, "update": true`? — rickhg12hs, Aug 22 '22 at 12:56
`denseRank` was a better option than `rank` because i can't allow gaps in my collection. ideally i would like something between both but i didn't find it. I don't especially care about the `score` value, i mean the first criteria is `"update": true` and IF `"update": true`, then check the `score` to see if it's not 0 and do some updates. This improves performances over my collection. — Tom3652, Aug 22 '22 at 13:41

rickhg12hs · Accepted Answer · 2022-08-22T15:17:03.580

This aggregation pipeline isn't particularly efficient (a fair amount of work in "$setWindowFields" gets thrown away - more comments about this below), but I think it does what you want. Please check to make sure it's correct as I don't have complete understanding of the collection, its use, etc.

N.B.: This aggregation pipeline is not very efficient because:

It processes every document. There's no leading "$match" to filter documents.
Because of 1., "$setWindowFields" has to "partitionBy": "$update" and sort/rank the "update": false partition and "$and": ["update": true, {"$lte": ["score", 0]}] docs even though it is irrelevant.
All the irrelevant work is thrown away by just setting the "update": false" partition's "rank" to 0 and then excluding all the "$and": ["update": true, {"$lte": ["score", 0]}] documents from the "$merge".

In a large collection, your original two-step update may likely be more efficient.

db.ranking.aggregate([
  {
    "$setWindowFields": {
      "partitionBy": "$update",
      "sortBy": {"score": -1},
      "output": {
        "rank": {"$denseRank": {}}
      }
    }
  },
  {
    "$set": {
      "rank": {
        "$cond": [
          "$update",
          "$rank",
          0
        ]
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$not": [{"$and": ["$update", {"$lte": ["$score", 0]}]}]
      }
    }
  },
  {"$merge": "ranking"}
])

Try it on mongoplayground.net.

Thank you so much for your answer, it's indeed working just fine. Can you please elaborate on the "fair amount of work" in `$setWindowFields` ? Please note that this query will run against a collection without millions of entries once per day from an Atlas Trigger, maybe more later... The question i am asking here is that your aggregation is more performant that having my previous aggregation along with a `ranking_col.updateMany({"update": false}, {$set: {"rank": parseInt(0, 10)}});` query ? — Tom3652, Aug 22 '22 at 14:51
Thanks for the explanations i understand better a lot of things now. I will make some benchmark tests to see performances, but my you answered my question so i accept your answer. — Tom3652, Aug 22 '22 at 16:34

Update multiple fields based on condition in aggregation pipeline MongoDB Atlas trigger

1 Answers1