By shesek


2014-05-05 10:49:37 8 Comments

I have documents that looks something like that, with a unique index on bars.name:

{ name: 'foo', bars: [ { name: 'qux', somefield: 1 } ] }

. I want to either update the sub-document where { name: 'foo', 'bars.name': 'qux' } and $set: { 'bars.$.somefield': 2 }, or create a new sub-document with { name: 'qux', somefield: 2 } under { name: 'foo' }.

Is it possible to do this using a single query with upsert, or will I have to issue two separate ones?

Related: 'upsert' in an embedded document (suggests to change the schema to have the sub-document identifier as the key, but this is from two years ago and I'm wondering if there are better solutions now.)

4 comments

@coyotte508 2019-08-09 08:55:54

There's a way to do it in two queries - but it will still work in a bulkWrite.

This is relevant because in my case not being able to batch it is the biggest hangup. With this solution, you don't need to collect the result of the first query, which allows you to do bulk operations if you need to.

Here are the two successive queries to run for your example:

// Update subdocument if existing
collection.updateMany({
    name: 'foo', 'bars.name': 'qux' 
}, {
    $set: { 
        'bars.$.somefield': 2 
    }
})
// Insert subdocument otherwise
collection.updateMany({
    name: 'foo', $not: {'bars.name': 'qux' }
}, {
    $push: { 
        bars: {
            somefield: 2, name: 'qux'
        }
    }
})

This also has the added benefit of not having corrupted data / race conditions if multiple applications are writing to the database concurrently. You won't risk ending up with two bars: {somefield: 2, name: 'qux'} subdocuments in your document if two applications run the same queries at the same time.

@bluenike 2018-02-14 22:32:19

There isn't a way to do this if the update relies on a reference to the record being updated (e.g., update x => x + 1). Issuing 2 separate commands (set then insert) results in a race condition that can't be solved by checking for duplicates, because if your insert is rejected due to a duplicate, you would lose the effect of your update (e.g., x would not be incremented properly in the example above). Would be nice if MongoDB added this functionality, since the ability to implement embedded documents are a big draw to MongoDB for certain applications.

@Neil Lunn 2014-05-06 07:25:41

No there isn't really a better solution to this, so perhaps with an explanation.

Suppose you have a document in place that has the structure as you show:

{ 
  "name": "foo", 
  "bars": [{ 
       "name": "qux", 
       "somefield": 1 
  }] 
}

If you do an update like this

db.foo.update(
    { "name": "foo", "bars.name": "qux" },
    { "$set": { "bars.$.somefield": 2 } },
    { "upsert": true }
)

Then all is fine because matching document was found. But if you change the value of "bars.name":

db.foo.update(
    { "name": "foo", "bars.name": "xyz" },
    { "$set": { "bars.$.somefield": 2 } },
    { "upsert": true }
)

Then you will get a failure. The only thing that has really changed here is that in MongoDB 2.6 and above the error is a little more succinct:

WriteResult({
    "nMatched" : 0,
    "nUpserted" : 0,
    "nModified" : 0,
    "writeError" : {
        "code" : 16836,
        "errmsg" : "The positional operator did not find the match needed from the query. Unexpanded update: bars.$.somefield"
    }
})

That is better in some ways, but you really do not want to "upsert" anyway. What you want to do is add the element to the array where the "name" does not currently exist.

So what you really want is the "result" from the update attempt without the "upsert" flag to see if any documents were affected:

db.foo.update(
    { "name": "foo", "bars.name": "xyz" },
    { "$set": { "bars.$.somefield": 2 } }
)

Yielding in response:

WriteResult({ "nMatched" : 0, "nUpserted" : 0, "nModified" : 0 })

So when the modified documents are 0 then you know you want to issue the following update:

db.foo.update(
    { "name": "foo" },
    { "$push": { "bars": {
        "name": "xyz",
        "somefield": 2
    }}
)

There really is no other way to do exactly what you want. As the additions to the array are not strictly a "set" type of operation, you cannot use $addToSet combined with the "bulk update" functionality there, so that you can "cascade" your update requests.

In this case it seems like you need to check the result, or otherwise accept reading the whole document and checking whether to update or insert a new array element in code.

@shesek 2014-05-07 11:53:45

Yep, that's what I currently have - $set followed by a $push if no matching documents were found... was just wondering if there's a better way to go about this. Thanks for the explanation!

@Michael Moser 2014-12-17 22:49:04

How do you make this atomic if you have multiple processes attempting to update at the same time. Seems like there could be a race condition and you might call $push more than once and end up with more than one record in the array. Is there a way to insert the new value in an atomic way?

@Neil Lunn 2015-01-03 10:13:00

@MichaelMoser Only just saw the comment. But by adding in inequality test like { "name": "foo", "bars.name": { "$ne": "xyz" } } as the query then you ensure that you are not duplicating "pushed" elements.

@Pila 2016-09-14 17:35:05

must the first parameter contain two elements? Can't I do: db.foo.update( { "name": "foo" }, { "$set": { "bars.$.somefield": 2 } }, { "upsert": true } ) if name is unique?

@nesdis 2017-01-06 07:19:12

if you dont mind changing the schema a bit and having a structure like so:

{ "name": "foo", "bars": { "qux": { "somefield": 1 },
                           "xyz": { "somefield": 2 },
                  }
}

You can perform your operations in one go. Reiterating 'upsert' in an embedded document for completeness

Related Questions

Sponsored Content

11 Answered Questions

[SOLVED] Query for documents where array size is greater than 1

34 Answered Questions

[SOLVED] How to query MongoDB with "like"?

16 Answered Questions

[SOLVED] How do I drop a MongoDB database from the command line?

  • 2012-01-13 21:11:40
  • coffee-grinder
  • 437553 View
  • 834 Score
  • 16 Answer
  • Tags:   mongodb

17 Answered Questions

[SOLVED] SQLite - UPSERT *not* INSERT or REPLACE

  • 2009-01-07 01:47:23
  • Mike Trader
  • 278747 View
  • 509 Score
  • 17 Answer
  • Tags:   sql sqlite upsert

6 Answered Questions

[SOLVED] MongoDB vs. Cassandra

10 Answered Questions

[SOLVED] When to use MongoDB or other document oriented database systems?

  • 2009-09-25 09:16:03
  • aurora
  • 119868 View
  • 508 Score
  • 10 Answer
  • Tags:   mysql mongodb

5 Answered Questions

[SOLVED] Bulk upsert in MongoDB using mongoose

2 Answered Questions

1 Answered Questions

[SOLVED] MongoDB: upsert with unique index

2 Answered Questions

[SOLVED] Upsert Document and/or add a Sub-Document

Sponsored Content