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 ?

CodePudding user response：

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:

1. It processes every document. There's no leading "$match" to filter documents.
2. 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.
3. 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.