[SERVER-3326] Support $ positional operator with an upsert Created: 24/Jun/11  Updated: 06/Dec/22  Resolved: 29/Jun/19

Status: Closed
Project: Core Server
Component/s: Write Ops
Affects Version/s: 1.8.2
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Reid Morrison Assignee: Backlog - Query Team (Inactive)
Resolution: Won't Do Votes: 45
Labels: findAndModify, insert, query
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

All


Issue Links:
Duplicate
is duplicated by SERVER-38729 Make $setOnInsert work with upserts a... Closed
Related
related to SERVER-6566 Support conditional updates: $updates Closed
Assigned Teams:
Query
Participants:

 Description   

According to the documentation, this limitation currently applies to upserts:
<quote>
The positional operator cannot be combined with an upsert since it requires
a matching array element. If your update results in an insert then the "$"
will literally be used as the field name.
</quote>

When performing a findAndModify it would be incredibly powerful to support the insert condition for a document that does not exist. It would save on multiple calls to the Mongo Server and allow for a single atomic update/insert for a document with embedded arrays.

Below is JavaScript that can be run in the Mongo Shell to demonstrate this powerful capability:

The purpose of these documents is keep track of the last login per user per type of login. And to create the user document if it does not already exist in a single atomic transaction.

db.user_identities.ensureIndex({"user" : 1}, {unique : true});
db.user_identities.save({
    user: 'user1',
    first_seen: 'date1',
    last_seen: [
        { type: 'TA', date: 'date1' }
    ]
})
 
db.user_identities.save({
    user: 'user2',
    first_seen: 'date2',
    last_seen: [
        { type: 'TA', date: 'date2' },
        { type: 'PP', date: 'date3' }
    ]
})
 
db.user_identities.save({
    user: 'user3',
    first_seen: 'date3',
    last_seen: [
        { type: 'TA', date: 'date3' },
        { type: 'PP', date: 'date4' }
    ]
})
 
// Existing entry - Works as expected
db.user_identities.findAndModify({
    query: {"user":"user3", "last_seen" : {$elemMatch : {"type": "PP", "date" : { $lte: "date4"} }}},
    update: {$set: {"last_seen.$":{ type: 'PP', date: 'date5' }}},
    upsert: true,
    "new": true}) 
 
/* Returns the following:
{
        "_id" : ObjectId("4e034dd979ee2fd2e506ce24"),
        "user" : "user3",
        "first_seen" : "date3",
        "last_seen" : [
                {
                        "type" : "TA",
                        "date" : "date3"
                },
                {
                        "type" : "PP",
                        "date" : "date5"
                }
        ]
} 
*/
 
// Non-existent - Inserts the $ as the element name in the new
document
db.user_identities.findAndModify({
    query: {"user":"user4", "last_seen" : {$elemMatch : {"type": "PP", "date" : { $lte: "date6"} }}},
    update: {$set: {"last_seen.$":{ type: 'PP', date: 'date6' }}},
    upsert: true,
    "new": true}) 
 
/* Returns:
{
        "_id" : ObjectId("4e034dd9f0e57cca9b6f045a"),
        "last_seen" : {
                "$" : {
                        "type" : "PP",
                        "date" : "date6"
                }
        },
        "user" : "user4"
 
}
*/



 Comments   
Comment by Asya Kamsky [ 29/Jun/19 ]

SERVER-40381 implemented support for aggregation expressions to specify update for 4.2.  (SERVER-40397 is the same thing for findAndModify).

You can see some examples here.

This can now be done by using various aggregation conditional and array expressions. I believe this can handle the examples/use cases that are mentioned in the comments.

The example mentioned:

db.user_identities.findAndModify({
    query: {"user":"user4", "last_seen" : {$elemMatch : {"type": "PP", "date" : { $lte: "date6"} }}},
    update: [
      {$set:{array:{$cond:{
            if: {$in:[ "PP" ,  "$last_seen.type"]},
            then:{$map:{ ... }} ,  // go through array when finding type "PP" set date to "date6"
            else: {$concatArrays: [ {$ifNull:["$last_seen",[]]},  [ {type:"PP",date:"date6" ] ]}
      }}}}
    ],
    upsert: true,
    "new": true}
)

Comment by Asya Kamsky [ 28/Jun/17 ]

On current version (3.4+) this update will return an error:

db.user_identities.findAndModify({
    query: {"user":"user4", "last_seen" : {$elemMatch : {"type": "PP", "date" : { $lte: "date6"} }}},
    update: {$set: {"last_seen.$":{ type: 'PP', date: 'date6' }}},
    upsert: true,
    "new": true
})
2017-06-28T12:09:30.668-0400 E QUERY    [thread1] Error: findAndModifyFailed failed: {
	"ok" : 0,
	"errmsg" : "The positional operator did not find the match needed from the query. Unexpanded update: last_seen.$",
	"code" : 16836,
	"codeName" : "Location16836"
} 

rather than inserting bad data.

Generated at Thu Feb 08 03:02:45 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.