Conditional Operations with findOneAndUpdate

[Kyaw_Zayar_Tun]

Hello,
Let me first provide the context of what is my database and what I want to with it. So, I have a database of song names and their ranks in music chart. What I want to do is

1. I will query a document with Song’s name
2. Then I want to check if Length of Array is more than certain length, like 50;
   if that condition is
   TRUE, more than 50, I want to pop the first element from array than push new data as element to that array.
   FALSE, less than 50, just push a new data as element to array.
3. I want to get the upsert option to be true as well incase if document has not been found. So I tried as the follows

let condition = {
    $cond:{
        if:{ $gte:[{$size:"Ranking"},10]},
        then:{$and:[{$pop:{Ranking: -1}},{$push:{Ranking:123}}]},
        else:{$push:{Ranking:345}}
    }
}
db.collection('musicChart').findOneAndUpdate({Song:'Beatbox'},{$expr:{Ranking:{condition}}},{upsert:true})

But I got an error of Unknown modifier: $expr.
I tried to search forums for the answers but I can’t find the exact of what I want to do. So I decided to post here.

[MaBeuLux88_xxx (Maxime Beugnet)]

Hi @Kyaw_Zayar_Tun and welcome in the MongoDB Community !

I think I got it working with all 3 scenarios: doc doesn’t exist, small size, big size.

Check out this script and its output below.

function updateRank(value) {
    return db.coll.findOneAndUpdate({Song: 'Beatbox'},
        [
            {
                $set: {
                    Ranking: {
                        $cond: {
                            if: {$gte: [{$size: {$ifNull: ["$Ranking", []]}}, 10]},
                            then: {$concatArrays: [{$slice: ["$Ranking", 1, 9]}, [value]]},
                            else: {$concatArrays: [{$ifNull: ["$Ranking", []]}, [value]]}
                        }
                    }
                }
            }
        ], {upsert: true, new: true});
}

db.coll.drop();
updateRank(1);
db.coll.updateOne({Song: "Beatbox"}, {"$set": {Ranking: [1, 2, 3, 4, 5, 6, 7, 8, 9]}});
updateRank(10);
updateRank(11);

Result in the console:

$ mongosh --quiet test < /tmp/mdb/query.js 
test [direct: primary] test> function updateRank(value) {
...     return db.coll.findOneAndUpdate({Song: 'Beatbox'},
.....         [
.....             {
.......                 $set: {
.........                     Ranking: {
...........                         $cond: {
.............                             if: {$gte: [{$size: {$ifNull: ["$Ranking", []]}}, 10]},
.............                             then: {$concatArrays: [{$slice: ["$Ranking", 1, 9]}, [value]]},
.............                             else: {$concatArrays: [{$ifNull: ["$Ranking", []]}, [value]]}
.............                         }
...........                     }
.........                 }
.......             }
.....         ], {upsert: true, new: true});
... }
[Function: updateRank]
test [direct: primary] test> 

test [direct: primary] test> db.coll.drop();
true
test [direct: primary] test> updateRank(1);
{
  _id: ObjectId("62aa12362b73caadd158f2de"),
  Song: 'Beatbox',
  Ranking: [ 1 ]
}
test [direct: primary] test> db.coll.updateOne({Song: "Beatbox"}, {"$set": {Ranking: [1, 2, 3, 4, 5, 6, 7, 8, 9]}});
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
test [direct: primary] test> updateRank(10);
{
  _id: ObjectId("62aa12362b73caadd158f2de"),
  Song: 'Beatbox',
  Ranking: [
    1, 2, 3, 4,  5,
    6, 7, 8, 9, 10
  ]
}
test [direct: primary] test> updateRank(11);{
  _id: ObjectId("62aa12362b73caadd158f2de"),
  Song: 'Beatbox',
  Ranking: [
    2, 3, 4,  5,  6,
    7, 8, 9, 10, 11
  ]
}

Cheers,
Maxime.

[Kyaw_Zayar_Tun]

Thanks, will check this out!!