mongoose update array or add to the array

Question

I've been trying to get this running for a while now but I can't figure out what I'm doing wrong.

I have two schemas like this

const paymentSchema = new Schema({
    year_month: {
        type: String,
        required: true
    },
    status: {
        type: Boolean,
        required: true
    }
});

const testSchema = new Schema({
    name: {
        type: String,
        required: true
    },
    payments: [{
        type: paymentSchema,
        required: false,
    }]
});

Then I want to update the existing value or if that value is not available I'd like to add it to the array.

Let's say I have this values in the DB:

[
    {
        "_id": "5e90ae0e0ed9974174e92826",
        "name": "User 1",
        "payments": [
            {
                "_id": "5e90c3fb79bba9571ae58a66",
                "year_month": "2020_02",
                "status": false
            }
        ]
    }
]

Now I'd like to change the status of year_month 2020_02 to true with this code and it works:

testSchema.findOneAndUpdate(
    {
        _id: '5e90ae0e0ed9974174e92826',
        payments: { $elemMatch: { year_month: '2020_02' }}
    },
    { $set: {
        'payments.$': {
            year_month: '2020_02',
            status: false
        }
      }
    },
    {
        new: true,
        upsert: true
    }
).then( result => {
    response.send(result);
});

The problem appears when I try to do this

testSchema.findOneAndUpdate(
    {
        _id: '5e90ae0e0ed9974174e92826',
        payments: { $elemMatch: { year_month: '2020_03' }}
    },
    { $set: { 
        'payments.$': {
            year_month: '2020_03',
            status: false
        }
      },
    },
    {
        new: true,
        upsert: true
    }
).then( result => {
    response.send(result);
});

I get this message from the upsert...

(node:8481) UnhandledPromiseRejectionWarning: MongoError: The positional operator did not find the match needed from the query.
    at Connection.<anonymous> (/home/vedran/Documents/Projekt/node_modules/mongodb/lib/core/connection/pool.js:466:61)
    at Connection.emit (events.js:223:5)
    at Connection.EventEmitter.emit (domain.js:475:20)
    at processMessage (/home/vedran/Documents/Projekt/node_modules/mongodb/lib/core/connection/connection.js:384:10)
    at TLSSocket.<anonymous> (/home/vedran/Documents/Projekt/node_modules/mongodb/lib/core/connection/connection.js:553:15)
    at TLSSocket.emit (events.js:223:5)
    at TLSSocket.EventEmitter.emit (domain.js:475:20)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:290:11)
    at TLSSocket.Readable.push (_stream_readable.js:224:10)
    at TLSWrap.onStreamRead (internal/stream_base_commons.js:181:23)
(node:8481) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:8481) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

According to the docs Mongoose.findOneAndUpdate() this should work but I'm making some mistake, and I can't figure out what exactly. I know the match query is the issue but I'm not sure how to change it so the upsert gets applied.

In the end I solved it like this:

testSchema.findOneAndUpdate(
    {
        _id: '5e90ae0e0ed9974174e92826',
        payments: { $elemMatch: { year_month: '2020_03' }}
    },
    {
        $set: {
            'payments.$': {
                year_month: '2020_02',
                status: false
            }
        }
    },
    {new: true}
).then( success => {
            // response === null if no match is found
            if( success ) {
                response.send(success);
            } else {
                testSchema.findOneAndUpdate(
                    { _id: '5e90ae0e0ed9974174e92826' },
                    {
                        $push: {
                            'payments': request.body
                        }
                    },
                    {new: true}
                ).then(success => {
                    response.send(success);
                });
            }
        },
        error => {
            response.send(error);
        }
);

But I'm making two requests here which could cause the race condition issues. 1. to update and 2. to add if it doesn't exist

I'd like to know if there is a better way to make it use the upsert and avoid the race conditions.

There is also a nice short tutorial on mongoose page which describes the upsert on findOneAndUpdate but it doesn't include arrays and this is probably what complicates the issue in my case.

Final solution based on the responses from joe & prasad_. Actually it is not that complicated once you take the time to understand what's going on here.

testSchema.findOneAndUpdate(
    { "_id": customerId },
    [{
        $set: {
            payments: {
                $cond: [

                    {
                        $gt: [
                            {
                                $size: {
                                    $filter: {
                                        input: "$payments", 
                                        cond: {
                                            $eq: [
                                                "$$this.year_month",
                                                testData.payments.year_month
                                            ]
                                        }
                                    }
                                }
                            },
                            0
                        ]
                    },

                    {
                        $reduce: {
                            input: "$payments",
                            initialValue: [],
                            in: {
                                $concatArrays: [
                                    "$$value",
                                    [{
                                        $cond: [
                                            { $eq: ["$$this.year_month", testData.payments.year_month] },
                                            { $mergeObjects: ["$$this", { status: testData.payments.status }] },
                                            "$$this"
                                        ]
                                    }]
                                ]
                            }
                        }
                    },

                    {
                        $concatArrays: [
                            "$payments",
                            [testData.payments]
                        ]
                    }
                ]
            }
        }
    }],
    { new: true }
).then( 
    success => {
        response.send(success);
    },
    error => {
        response.send(error);
    }
);

basically do u mean, if the query cannot find an object then it should create one? This is the problem statement right? — Aritra Chakraborty, Apr 15 '20 at 17:16
Does this answer your question? [MongoDB: upsert sub-document](https://stackoverflow.com/questions/23470658/mongodb-upsert-sub-document) — Aritra Chakraborty, Apr 15 '20 at 17:24
@AritraChakraborty yes and no... the problem is that document already exists. The array is the document, right? That is why the upsert is not working. — Vedran, Apr 15 '20 at 18:09
@AritraChakraborty thanks for the link. That is the same problem I'm having here. Was looking but did not saw that one. So... it looks like I can leave it as is or maybe change it to get the result from the update query and do a second query if I need to. — Vedran, Apr 15 '20 at 18:16

score 1 · Accepted Answer · answered Apr 16 '20 at 01:00

The main problem is that findOneAndUpdate does exactly what its name implies. It executes a find using the provided filter, and if a match is found, applies the updates to the first matching document.

If the collection contains only this document:

[
    {
        "_id": "5e90ae0e0ed9974174e92826",
        "payments": [
            {
                "year_month": "2020_02",
                "status": false
            }
        ]
    }
]

The initial find part is essentially

.find({
        _id: '5e90ae0e0ed9974174e92826',
        payments: { $elemMatch: { year_month: '2020_03' }}
})

This matches nothing, and since upsert is set to true, fineOneAndUpdate attempts to create a brand new document. Even if it were able to create an array from an unmatched positional operator, the document it would be trying to add would be:

 {
        "_id": "5e90ae0e0ed9974174e92826",
        "payments": [
            {
                "year_month": "2020_03",
                "status": false
            }
        ]
}

This is not correct, and would fail to insert due to duplicate _id value anyway.

If you are using MongoDB 4.2, you could use an aggregation pipeline as the second argument to findAndUpdate to check the array for the element you are interested in and add it if it is missing.

One not very pretty method is below. The findOneAndUpdate will match the _id, and the pipeline will:
- check if any element in the array matches the desired year_month
- If so, $reduce the array to update the status field in that element
- If not, append a new element
- Assign the result back to payments

.findOneAndUpdate(
    { "_id": "5e90ae0e0ed9974174e92826" },
    [{$set: {
         payments: {$cond:[
                 {$gt:[
                       {$size:
                             {$filter:{
                                  input:"$payments", 
                                  cond:{$eq:["$$this.year_month","2020_03"]}
                       }}},
                       1
                  ]},
                  {$reduce:{
                        input:"$payments",
                        initialValue:[],
                        in:{$concatArrays:[
                                  "$$value",
                                  [{$cond:[
                                       {$eq:["$$this.j",3]},
                                       {$mergeObjects:["$$this",{status:true}]},
                                       "$$this"
                                  ]}]
                        ]}
                  }},
                  {$concatArrays:[
                       "$payments",
                       [{year_month:"2020_03", status:true}]
                  ]}
          ]}
     }}]
)

score 1 · Answer 2 · answered Apr 16 '20 at 09:43

Consider the two input documents. The first one will be inserted (the year_month: '2020_03' doesn't exist in the payments array). When the update is run with the second one it will update the statusof the existing sub-document in the array.

The update operation is valid with the MongoDB version 4.2 or later only, as it uses a pipeline for the update.

INPUT_DOC = { year_month: '2020_03', status: false }    // this will get inserted into the array
INPUT_DOC = { year_month: '2020_02', status: true }     // updates the sub-document

db.collection.findOneAndUpdate(
  { 
      _id: "5e90ae0e0ed9974174e92826" 
  },
  [ 
      { 
          $set: { 
              payments: {
                  $reduce: {
                      input: "$payments", 
                      initialValue: { payments: [], update: false },
                      in: {
                          $cond: [ { $eq: [ "$$this.year_month", INPUT_DOC.year_month ] },
                                   { 
                                      payments: { 
                                          $concatArrays: [
                                               [ { _id: "$$this._id", year_month: "$$this.year_month", status: INPUT_DOC.status } ],
                                               "$$value.payments"
                                           ] 
                                      }, 
                                      update: true
                                   },
                                   { 
                                      payments: { 
                                          $concatArrays: [  [ "$$this" ], "$$value.payments" ] 
                                      }, 
                                      update: "$$value.update" 
                                   }
                          ]
                      }
                  }
              }
          }
      },
      { 
          $set: { 
              payments: { 
                  $cond: [ { $eq: [ "$payments.update", false ] },
                           { $concatArrays: [ [ INPUT_DOC ], "$payments.payments" ] },
                           { $concatArrays: [ [ ], "$payments.payments" ] }
                  ] 
              }
          }
      }
  ],
  { 
      new: true, 
      upsert: true 
  }
)

mongoose update array or add to the array

2 Answers2

Linked