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.

The upsert: true applies to the document, i.e. you insert a new document if it does not exist. It does not apply to an element in an array. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jul 2, 2021 at 13:43

turivishal · Accepted Answer · 2021-07-02 13:39:12Z

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 }
)

Collectives™ on Stack Overflow

How to upsert with MongoDB array?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related