findOneAndUpdate aggregation pipeline question

Working with Data
,
1

Can we do something like findOneAndUpdate set salary = 100 where empId nin (select empId where {some condition}) using aggregation pipeline?

2

Hi @Abhishek_Kumar_Singh,

Just to make it clear you want to update collection A based on a list of ids from collection B ?

Why not to do it in 2 statements?

If this is the case I am not sure why you are using find one as it will fetch only one doc.

Anyway , I think only $merge aggregation can work and not update for a single statement…

Thanks
Pavel

1 Like
3

Hello Pavel!

I want to update collection A based a list of id in collection A itself.

Collection schema;
ClassId, lastAllotedTime, studentId

there are 40 seats in each class.

So initially our collection will look something like:

ClassId, lastAllotedTime, studentId
A null null
A null null
.
.
.
.
A null null
B null null
B null null
.
.
.
.
B null null
C null null
C null null
.
.
.

A class has many seats. I want to assign a seat in class to a student if no seat has been assigned from this class in last 10 minutes.

So my query should be "if no slot assigned from this class in last 10 minutes, assign one slot to student.
Next student should not be assigned a slot in same class if he comes the next second.

db.getCollection(‘classslots’).findOneAndUpdate(
{lastAllotedTime: {$lt: new Date((new Date())-10001060)}, “studentId” : null},
{ $set : { “studentid” : “foobar”, “lastAllotedTime” : new Date()}})

I want to add one more condition here that classId nin (classIds of classes which were allotted a slot in last 10 minutes)

I want to exploit atomicity of search and update of findOneAndUpdate, If i do use two statements i’ll have to use lock, which I want to avoid at all costs.

Thank you so much for your prompt reply. Really appreciate it.

4

Hi @Abhishek_Kumar_Singh,

Why not to hold the assignment of a specifc class in an array of 40 slots.

{
_id : ....,
classId : "A",
seatsAssigned : [
 { studentId : "xxxx"},
 { studentId : "bbb"}
...
],
totalAssigned : 10,
freeToAssgin: 30,
lastAssigned : ISODate("2021-03-02T10:00")
}

Now you can index the fields and do an update like:

db.classes.findOneAndUpdate({lastAssigned : {$lt : {NOW - 10min}},  freeToAssgin : {$gt : 0}}, 
{ $addToSet : { seatsAssigned : {studentId : "zzz" } }, 
$inc : { freeToAssgin: -1, freeToAssgin: 1 }, 
$set :{  lastAssigned : new Date() }})

Using embedded related documents help with atomicity in MongoDB and is the correct design for document models.

Best regards,
Pavel

1 Like
5

Thank you so much Pavel. It was really helpful :smiley:

6

@Pavel_Duchovny, just one more follow up please, I am trying to update priority of class if more than 3 seats has been assigned.

db.classes.findOneAndUpdate({lastAssigned : {$lt : {NOW - 10min}},  freeToAssgin : {$gt : 0}}, 
{ $addToSet : { seatsAssigned : {studentId : "zzz" } }, 
$inc : { freeToAssgin: -1, freeToAssgin: 1 }, 
$set :{  lastAssigned : new Date() },
$set: {
      "priority": {
        "$cond": {
          if: {
            $gte: ["$seatsAssigned", 3]
          },
          then: 150,
          else: 1,
        }
      }
    }
})

I am running mongo 4.4.4 version but i keep running into

Failed to execute script.
Error: findAndModifyFailed failed: {
"ok" : 0,
"errmsg" : "The dollar ($) prefixed field '$cond' in 'priority.$cond' is not valid for storage.",
"code" : 52,
"codeName" : "DollarPrefixedFieldName"
}

Details:

_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCollection.prototype.findAndModify@src/mongo/shell/collection.js:736:1
DBCollection.prototype.findOneAndUpdate@src/mongo/shell/crud_api.js:857:12
@(shell):1:1

Is this the right way to achieve conditional updates?

7

Hi @Abhishek_Kumar_Singh,

So you can use aggregation $cond in aggregation pipeline update syntax.

It changes the operators as you can’t use update operators in a pipeline and need to use $add and $setUnion when changing fields instead of $addToSet and $inc, please note that the second pat also start with array pipeline []:

db.classes.findOneAndUpdate({lastAssigned : {$lt : new Date((new Date())-10001060)}, freeToAssgin : {$gt : 0}},
[{$set: {
  seatsAssigned : { $setUnion : ["$seatsAssigned", [{studentId : "zzz"}]]},
  totalAssigned : { $add : ["$totalAssigned", 1] },
  freeToAssgin : { $add : ["$totalAssigned", -1] },
  lastAssigned : new Date()
}}, {$set: {
  priority: {
"$cond": {
if: {
$gte: ["$totalAssigned", 3]
},
then: 150,
else: 1,
}
}
}}]);

Thanks,
Pavel

8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.