How to upsert with MongoDB array?

Question

I am trying to insert, update values in the MongoDB array.
My MongoDB version is 4.0.5.

Here is my collection :

{
    'id': 1,
    'array': [{
        'code': 'a'
    }, {
        'code': 'b'
    }]
}

I am trying to make some upsert queries to insert/update into an array but I don't found a good solution until then.

My filters are :

'id' (to point the correct document)
'array.code' (to point the correct array cell)

If the document exists in the collection but there is no cell with 'code': 'c'

db.test.update({
        'id': 1
    }, {
        $set: {'array.$[elem].test':'ok'}
    }, {
        upsert: true,
        arrayFilters: [{'elem.code': 'c'}]
    }
)

I have no error but no upsert too.
I want to insert the element in the array like this :

// Desired result
{
    'id': 1,
    'array': [{
        'code': 'a'
    }, {
        'code': 'b'
    }, {
        'code': 'c'
        'test': 'ok'
    }]
}

If the document doesn't exist in the collection

db.test.update({
        'id': 3
    }, {
        $set: {'array.$[elem].test':'ok'}
    }, {
        upsert: true,
        arrayFilters: [{'elem.code': 'a'}]
    }
)

In that case, I have this error :

WriteError: The path 'array' must exist in the document in order to apply array updates., full error: {'index': 0, 'code': 2, 'errmsg': "The path 'array' must exist in the document in order to apply array updates."}

I want to upsert a new document with elements of the query like this :

// Desired result
{
    'id': 3,
    'array': [{
        'code': 'a'
        'test': 'ok'
    }]
}

upsert: true in the query parameters doesn't seem to work with the array.
Your help will be highly appreciated.

user_8987128 · Answer

The upsert is not effective in the array, If you do update MongoDB version from 4.0.5 to 4.2 then you can able to use update with aggregation pipeline starting from MongoDB 4.2,

Case 1: If the document exists in the collection but there is no cell with 'code': 'c':

var id = 2;
var item = { code: "c", test: "ok" };

Playground

Case 2: If the document doesn't exist in the collection:

var id = 3;
var item = { code: "a", test: "ok" };

Playground

$ifNull to check if the field does not exist then return empty
$cond to check if input code is in array
- yes, then $mep to iterate loop of array and check condition if match then update other fields otherwise return an empty object
- $mergeObjects to merge current object with updated fields
- no, $concatArrays to concat current array with new item object

db.collection.update(
  { "id": id },
  [{
    $set: {
      array: {
        $cond: [
          {
            $in: [item.code, { $ifNull: ["$array.code", []] }]
          },
          {
            $map: {
              input: "$array",
              in: {
                $mergeObjects: [
                  "$$this",
                  {
                    $cond: [{ $eq: ["$$this.code", "c"] }, item, {}]
                  }
                ]
              }
            }
          },
          {
            $concatArrays: [{ $ifNull: ["$array", []] }, [item]]
          }
        ]
      }
    }
  }],
  { upsert: true }
)

How to upsert with MongoDB array?

1 Answers