Can mongo upsert array data?

Question

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?

It amazes me that mongo still doesn't have this basic functionality that I run into multiple times in every project I've used mongo with :/ — Dominic, Oct 15 '18 at 22:04
In 2022, Mongo does have some options with aggregate pipeline updates. See https://stackoverflow.com/questions/37427610/mongodb-update-or-insert-object-in-array/65967584#65967584 and https://stackoverflow.com/questions/37427610/mongodb-update-or-insert-object-in-array/72434562#72434562. Both are rather verbose but get the job done. — Richard Scarrott, Nov 11 '22 at 14:14

score 25 · Answer 1 · answered Sep 09 '13 at 22:34

25

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.

answered Sep 09 '13 at 22:34

Rob Watts

6,866
3
39
58

Thanks, it works well when searching using 2 fields in document – ruX Dec 05 '13 at 09:52
1

Add a `$exists` check in `$push` query, so that it fails if something happens between the 2 calls. – S.D. Nov 10 '17 at 14:01
1

I am looking for a solution to this problem as well - some years later. Is there any updates to this? – DauleDK Mar 05 '19 at 18:18
@DauleDK a quick search turned this up: https://docs.mongodb.com/manual/reference/method/Bulk.find.upsert/ – Rob Watts Mar 05 '19 at 20:13
Do you think bulk-find-upsert can solve this problem in one query? – DauleDK Mar 05 '19 at 21:45
This is a cool solution, but it has a drawback though. It changes the order of items in the array whenever there is an update. The updated item always goes to the end of the array, which is undesirable if the order of items is important. – Ahmad Jan 13 '21 at 20:53
Is this not possible even now? i.e. in a single call. – Subham Saha Jul 02 '21 at 19:56
@SubhamSaha looking at the docs I still don't see a way to do it in one call (and this question still getting traffic after 8 years also suggests that), but it looks like there may be better ways to do it in two calls now. It's probably worth updating my answer to include the new info. – Rob Watts Jul 02 '21 at 20:39

score 23 · Accepted Answer · answered Nov 27 '12 at 23:04

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.

Note that some form of "virtual collection" support for embedded documents is a popular request .. you can vote/watch [SERVER-142](https://jira.mongodb.org/browse/SERVER-142) in the MongoDB issue tracker. — Stennie, Nov 27 '12 at 23:06

score 6 · Answer 3 · edited May 23 '17 at 12:24

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

This works, but beware that you will lose the ability to index the items by ID, since mongo doesn't have a way to create an index on all keys of a given sub-object. — Dobes Vandermeer, Oct 05 '17 at 00:25

score 2 · Answer 4 · answered Jan 15 '20 at 09:08

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

Blake Mitchell · Answer 5 · 2023-02-13T22:31:38.237

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.

score 1 · Answer 6 · answered Aug 28 '19 at 05:08

1

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

answered Aug 28 '19 at 05:08

Shan

11
1

8

This would be very useful, but in my hands it only works if the identifiers are the same in the update object and the array filter, e.g.: `db.collection.update({'tester': 'tom'}, { $set: { "array.$[element].letter": "c"}}, { arrayFilters: [ { "element.letter": "b"} ]})` - which is much less helpful. Also, the `upsert` option mentioned in the docs allows you to insert a new document if one is not matched, and not add a new element to the array, so this doesn't solve the OPs problem. – danj1974 Nov 29 '19 at 16:02

Can mongo upsert array data?

6 Answers6

Linked

Related