CODEWITHSUNDEEP
node.jsmongodb
ThomasReggi
ThomasReggi

Reputation: 59345

Can mongo upsert array data?

I have a mongo document like this.

{
    "_id" : ObjectId("50b429ba0e27b508d854483e"),
    "array" : [
        {
            "id" : "1",
            "letter" : "a"
        },
        {
            "id" : "2",
            "letter" : "b"
        }
    ],
    "tester" : "tom"
}

I want to be able to insert and update the array with a single mongo command and not use a conditional within a find() then run insert() and update() depending on the presence of the object.

The id is the item I want to be the selector. So if I update the array with this:

{
    "id" : "2",
    "letter" : "c"
}

I have to use a $set statement

db.soup.update({
    "tester":"tom",
    'array.id': '2'
}, {
    $set: {
        'array.$.letter': 'c'
    }
})

And if I want to insert a new object into the array

{
    "id" : "3",
    "letter" : "d"
}

I have to use a $push statement

db.soup.update({
    "tester":"tom"
}, {
    $push: {
        'array': {
            "id": "3",
            "letter": "d"
        }
    }
})

I need a sort of upsert for an array item.

Do I have to do this programmatically or can I do this with a single mongo call?

Upvotes: 60

Views: 40898

Answers (6)

Blake Mitchell
Blake Mitchell

Reputation: 2787

With mongo 4.2 adding support for aggregation pipelines in update I was able to use $set, $concatArrays, $ifNull, and $filter to pull this together in a single call.

db.soup.updateOne(
  { tester: 'tom' },
  [
    {
      $set: {
        array: {
          $concatArrays: [
            [{ id: '2', letter: 'c' }],
            {
              $ifNull: [
                {
                  $filter: {
                    input: '$array',
                    as: 'item',
                    cond: {
                      $ne: [
                        '$$item.id',
                        '2',
                      ],
                    },
                  },
                },
                [],
              ],
            },
          ],
        },
      },
    },
  ],
)

This will replace the item where id is '2', if no other items in the array match, it will append the item.

Upvotes: 3

lukas_o
lukas_o

Reputation: 4236

The question is from 2012. I had a look into the mongo documentation: https://docs.mongodb.com/manual/reference/method/Bulk.find.upsert which Rob Watts mentioned in a comment, but it is very sparse.

I found an answer here: https://stackoverflow.com/a/42952359/1374488

Upvotes: 2

Shan
Shan

Reputation: 11

Hey You can do by using array filters option. You can refer here https://docs.mongodb.com/manual/reference/method/db.collection.update/#update-arrayfilters.

   db.getCollection('soup').update({'tester':tom}, { $set: { "collection.$[element].letter" : 'c' } },
   {
     arrayFilters: [ { "element.id": 2 } ]
   })

Upvotes: 1

G-Wiz
G-Wiz

Reputation: 7426

You can achieve upserts of embedded document sets in some cases if you can restructure your documents to use objects instead of arrays. I just answered this on another question but will adapt it here for convenience.

An example document would be

{
    tester: 'tom',
    items: {
        '1': 'a',
        '2': 'b'
    }
}

To upsert an item with id 3 and value 'c', you'd do

db.coll.update(
    { tester: 'tom' }, 
    { $set: { 'items.3': 'c' } }
)

One drawback is that querying for field names (using the $exists query operator) requires scanning. You can get around this by adding an extra array field that stores the property names. This field can be indexed and queried normally. Upserts become

db.coll.update(
    { tester: 'tom' }, 
    { 
        $set: { 'items.3': 'c' },
        $addToSet: { itemNames: 3 }
    }
)

(Keep in mind $addToSet is O(n).) When removing a property, you have to pull it from the array as well.

db.widgets.update(
    { tester: 'tom' }, 
    { 
        $unset: { 'items.3': true },
        $pull: { itemNames: 3 }
    }
)

Upvotes: 6

Rob Watts
Rob Watts

Reputation: 7146

I just ran into this problem myself. I wasn't able to find a one-call solution, but I found a two-call solution that works when you have a unique value in your array elements. Use the $pull command first, which removes elements from an array, and then $push.

db.soup.update({
    "tester":"tom"
}, {
    $pull: {
        'array': {
            "id": "3"
        }
    }
})
db.soup.update({
    "tester":"tom"
}, {
    $push: {
        'array': {
            "id": "3",
            "letter": "d"
        }
    }
})

This should work when the document doesn't exist, when the document exists but the entry in the array doesn't exist, and when the entry exists.

Again, this only works if you have something, like the id field in this example, that should be unique across elements of the array.

Upvotes: 25

Stennie
Stennie

Reputation: 65303

I'm not aware of an option that would upsert into an embedded array as at MongoDB 2.2, so you will likely have to handle this in your application code.

Given that you want to treat the embedded array as sort of a virtual collection, you may want to consider modelling the array as a separate collection instead.

You can't do an upsert based on a field value within an embedded array, but you could use $addToSet to insert an embedded document if it doesn't exist already:

db.soup.update({
    "tester":"tom"
}, {
    $addToSet: {
        'array': {
            "id": "3",
            "letter": "d"
        }
    }
})

That doesn't fit your exact use case of matching by id of the array element, but may be useful if you know the expected current value.

Upvotes: 23

Related Questions