Using $if Condition with $or for updating multiple MongoDB fields in one go

Question

I am trying to make the following update

if offer == {} OR offer.eta < now THEN offer = new_offer_obj ELSE offer = offer

AND another simultaneous update

if offer.eta < now THEN offer_list.push(offer) ELSE offer_list = offer_list

--- ALL of the above in a one go ---

Inside a MongoDB doc, offer is an object with fields:

offer: {info: {...}, eta: "<ISO-Time-String>"}

offer_list: is simply an array list of the above offer Object

offer_list: []

--- BELOW this is what I tried ---

Here is the code I tried after referencing other answers on SO. This does not work correctly. Not sure what is wrong here.

const now = new Date().getTime()
const res = await collection.update(
                { "_id": 12345 },
                { 
                    $set: { 
                        offer: { $cond: { $if: { $or:[ {$lt:["$offer.eta", now]}, {$eq:["$offer", {}]}] }},  then: new_offer_obj,  else:"$offer" },
                        offer_list: {
                            $cond: [{ $lt:["$offer.eta", now]}, 
                            {$push: { offer_list: "$offer" }}, 
                            "$offer_list" ] 
                        }
                    }
                },
                { 
                    "returnDocument" : "after", 
                    "returnOriginal" : false
                }
            )

For offer I tried using one type of syntax and for offer_list I tried using another type to experiment which one works. It'd be great if someone could help.

Answer 1

There are a lot of conditions and assumptions in this question, among them trying to compare ISO8601 strings against real dates, whether offer_list is initialized to [] or if it is unset at the start, etc. So the solution below "works" but should not be considered bulletproof. It is mostly a matter of using the pipeline form of update and getting the date comparison working. Given input like this:

now = new ISODate('2022-02-26');

new_offer_obj = {info: {"NEW":"OFFER"}, eta: "2022-06-02T00:00:00Z"};

var r = [
    {
        'desc':"empty initial offer so this gets JUST new offer obj",
        'offer': {}
    // does not matter if offer_list is [] or not                              
    }
    ,{
        'desc':"offer is not empty but eta > now so NOTHING HAPPENS",
        'offer': {info: {a:1}, eta: "2022-02-27T00:00:00Z"}
    }
    ,{
        'desc':"offer not empty AND eta < now so replace offer with new one and push existing onto offer list",
        'offer': {info: {a:1}, eta: "2022-02-02T00:00:00Z"},
        'offer_list': ['foo']
    }
];

then this update:

rc = db.foo.update(
    {},
    [
        // If no offer.eta, assume a very distant date; then convert in either case to real datetime:
        {$addFields: {XD: {$toDate: {$ifNull:['$offer.eta','9999-01-01']}}} },
        {$set: {
            offer: { $cond: [
                {$or:[{$lt:['$XD',now]},{$eq:["$offer",{}]}]},
                new_offer_obj,
                '$offer'
                ]
            },
            offer_list: { $cond: [
                { $lt:['$XD', now]},
                // $offer is an object so wrap with [] to make an array of 1:
                {$concatArrays: [ "$offer_list", ['$offer'] ]},
                "$offer_list"
            ]
                        }
        }},
        {$unset: 'XD'}
    ],
    {multi:true}
);

produces these mods:

{ "nMatched" : 3, "nUpserted" : 0, "nModified" : 2 }
{
    "_id" : 0,
    "desc" : "empty initial offer so this gets JUST new offer obj",
    "offer" : {
        "info" : {
            "NEW" : "OFFER"
        },
        "eta" : "2022-06-02T00:00:00Z"
    }
}
{
    "_id" : 1,
    "desc" : "offer is not empty but eta > now so NOTHING HAPPENS",
    "offer" : {
        "info" : {
            "a" : 1
        },
        "eta" : "2022-02-27T00:00:00Z"
    }
}
{
    "_id" : 2,
    "desc" : "offer not empty AND eta < now so replace offer with new one and push existing onto offer list",
    "offer" : {
        "info" : {
            "NEW" : "OFFER"
        },
        "eta" : "2022-06-02T00:00:00Z"
    },
    "offer_list" : [
        "foo",
        {
            "info" : {
                "a" : 1
            },
            "eta" : "2022-02-02T00:00:00Z"
        }
    ]
}

An interesting alternative is available if using v4.4 or higher. Starting in v4.4, the $merge stage can write back to the same collection that is being processed, which can turn an aggregate function into a "giant" update function:

c=db.foo.aggregate([
    {$addFields: {XD: {$toDate: {$ifNull:['$offer.eta','9999-01-01']}}} },
    {$project: {
        offer: { $cond: [
            {$or:[{$lt:['$XD',now]},{$eq:["$offer",{}]}]},
            new_offer_obj,
            '$offer'
            ]
        },
        offer_list: { $cond: [
            { $lt:['$XD', now]},
            {$concatArrays: [ "$offer_list", ['$offer'] ]},
            "$offer_list"
            ]
        }
    }},

    // Write the docs back onto foo.  Because of the $project above, only _id,
    // offer, and offer_list are flowing through the pipeline now.  We will
    // match on _id (fast!) and just like $mergeObjects, we will merger
    // the new offer and offer_list on top of the existing material.
    // We set 'whenNotMatched' to 'fail' because there are no normal conditions where we
    // cannot match on the same _id coming through the pipeline:
    // 
    {$merge: {
        into: "foo",
        on: [ "_id" ],
        whenMatched: "merge",
        whenNotMatched: "fail"
    }}
]);