setOnUpdate in mongo with one query on upsert?

Question

I have been googling and read the mongo docs, but find it weird no one asked for this so I thought I face the problem the wrong ways, but I am persistent so I am going to ask here, I know there is a setOnInsert operator in mongo when using upsert:true the fields on setOnInsert will be inserted on insert but ignored on update, but what I want is that I want some fields only get saved on update and ignored on insert (upsert) the reverse of that setOnInsert in a single query.

So here is the case I have field "createdDate" and "updatedDate" with upsert:true and using setOnInsert I can have the field createdDate on setOnInsert but for the "updatedDate" if I put it in $set it will get saved on insert (upsert) I want that "updatedDate" only get saved on update but ignored on insert (upsert) in one query. I can do this with 2 query and simpler but looking for a single query solution, also find it weird mongo doesn't have the reverse of setOnInsert yet , maybe this case is unusual?

I tried using $switch and $cond but it got inserted as document doesn't work, I tried in go driver for mongo, but accepting answer as mongo query / shell too (will convert them myself then)

coll := mongoCon.Mongo.Collection("productModels")
productId, _ := primitive.ObjectIDFromHex("65b87bbc571dd2a8d301c9f2")
doc := bson.D{
        {Key: "$set", Value: bson.D{
            {Key: "modelName", Value: "orange 11"},
            {Key: "updatedDate", Value: bson.M{
                "$switch": bson.M{
                    "branches": bson.A{
                        bson.M{
                            "case": bson.M{
                                "$eq": bson.A{
                                    bson.M{"_id": bson.M{"$exists": false}}, false,
                                }},
                            "then": nil,
                        },
                    },
                },
            },
            },
        }},
        {Key: "$setOnInsert", Value: bson.D{{Key: "createdDate", Value: time.Now()}}},
    }
    res, err := coll.UpdateOne(context.Background(), bson.M{"product_id": productId}, doc, options.Update().SetUpsert(true))
    // throw res here now, might use it later for 2 query solution
    _ = res
    if err != nil {
        log.Fatal(err)
    }

Answer 1

In my opinion, the simplest solution is to let updatedDate be inserted even for new documents. If you must tell if the document was ever updated, you can compare the updatedDate and the createdDate, or have a separate modifiedCount field maintained.

Now on to solve your requirement. Your second attempt to use $switch doesn't work because that is an aggregation operator, and to use an aggregation pipeline with an update operation, you have to use an array (or slice) document as the update document, this is what triggers interpreting it as an aggregation pipeline. For details, see Golang and MongoDB - I try to update toggle boolean using golang to mongodb but got object instead.

Doing so will introduce a new issue: you can't use $setOnInsert anymore. And you can't use the $exists operator in an aggregation pipeline :(

But what you want is achievable using a single $set stage: you may use $ifNull to retrieve a field's value if it exists, or provide a fallback value if it doesn't.

So the idea is to use the current value of createdDate if it exists, else pass and set the current time.

Similarly we can only update updatedDate if the createdDate already exists, which we can check using the above mentioned $ifNull operator (and using a $cond to tell if the result is null).

So you can do it like this:

now := time.Now()
res, err := coll.UpdateOne(ctx,
    bson.M{"product_id": productId},
    []any{
        bson.M{
            "$set": bson.M{
                "modelName": "orange 11",
                "updatedDate": bson.M{"$cond": []any{
                    bson.M{"$eq": []any{
                        bson.M{"$ifNull": []any{"$createdDate", nil}}, nil},
                    },
                    nil, now,
                }},
                "createdDate": bson.M{"$ifNull": []any{"$createdDate", now}},
            },
        },
    },
    options.Update().SetUpsert(true),
)

(Note I used bson.M for simplicity, you can translate it to using bson.D if field order matters to you, in which case it'll get more verbose.)

But again, I would anytime prefer the below solution instead of the above "ugliness" (not to mention this is probably faster):

now := time.Now()
res, err = c.UpdateOne(ctx,
    bson.M{"product_id": productId},
    bson.M{
        "$set": bson.M{
            "modelName":   "orange 11",
            "updatedDate": now,
        },
        "$setOnInsert": bson.M{
            "createdDate": now,
        },
    },
    options.Update().SetUpsert(true),
)