Toggle Mongo Document Date Property With Single Query

Question

Knowing just the _id of a document in Mongo is there a straightforward way to write ONE update query to do the following? I think the answer is "no", but I'd like to know about it if there is something that does let this happen.

Target a document by _id (single document).
If the readAt field exists on the document, remove it
If the readAt field does NOT exist on the document, set it to the current time

Essentially, I want to "toggle" the "read" state (using a Date as a flag) of the document without actually first fetching the document.

Currently I'm using something like:

collection.update(
  {_id: notificationDoc._id},
  notificationDoc.readAt
    ? {$unset: {readAt: true}}
    : {$set: {readAt: new Date()}}
)

which works fine, but requires I first read in that field. I'm not really worried about race conditions or anything, I'd just prefer to cut out the round trip to the DB.

I've thought of a couple of ways to do it with two update queries in serial with distinct conditions, but that doesn't seem like an improvement.

namar sood · Accepted Answer · 2020-07-08T09:29:13.267

1

If your MongoDB version is >= 4.2 then you can specify aggregate pipeline in you update query.

db.collection.update(
{ _id: notificationDoc._id },
[
  {
    $set: {
      readAt: {
        $cond: [
          {
            $lte: [
              "$readAt",
              null
            ]
          },
          "$$NOW",
          "$$REMOVE" //(previously null)
        ]
      }
    },
    
  }
])

Above query will set readtAt to null (or remove it if $$REMOVE is provided) if it exists and to current date time if not exist/null

You can see how the comparison works

edited Jul 08 '20 at 09:29

answered Jul 07 '20 at 22:37

namar sood

1,580
1
9
17

Ah, thanks. That actually looks pretty close. The goal would be to $unset it if it existed rather than set it to null. It looks like `$cond` always specifies one of two values from the documentation. Perhaps it is possible to have the second stage in the pipeline be an $unset on the field conditioned on it having a value of null. I may try to track down if that's possible with aggregation pipelines now I see that's possible in 4.2. – Chase Jul 07 '20 at 23:03
I have updated my answer, thanks to @prasad_ +1 for introducing the `$$REMOVE` – namar sood Jul 08 '20 at 09:22
it will now remove the `readAt` key if it doesn't exist – namar sood Jul 08 '20 at 09:23
1

After a couple of tests, looks like it works great! Learned something new. Thanks for revisiting it. – Chase Jul 08 '20 at 16:58

score 1 · Answer 2 · answered Jul 08 '20 at 05:31

The update (toggle) logic can be implemented as follows - I will explain with an example. Consider a collection with two documents:

{ "_id" : 1, "readAt" : "2020-07-01", "fld" : "Y" }
{ "_id" : 2, "fld" : "N" }

The following aggregation removes the readAtfield in the document with _id: 1 and sets the readAt: "2020-07-08" for the document with _id: 2.

var NEW_VALUE = "2020-07-08";

db.collection.aggregate([
  { 
      $addFields: { 
          readAt: { 
              $let: {
                  vars: { readAtExists: { $ifNull: [ "$readAt", null ] } },
                  in: { $cond: [ {$eq: [ "$$readAtExists", null ]}, NEW_VALUE, "$$REMOVE"  ] }
              }
          }
      }
  }
])

Based on this aggregation you can update the document in one of the following two ways. The first option works with MongoDB versions 4.2 or newer (uses Update with Aggregation Pipeline), and the second option works with versions earlier than the 4.2.

1) Update with Aggregation Pipeline

db.collection.update(
  { _id: 1 },  // { _id: 2 }
  [
    { 
        $set: { 
            readAt: { 
                $let: {
                    vars: { readAtExists: { $ifNull: [ "$readAt", null ] } },
                    in: { $cond: [ {$eq: [ "$$readAtExists", null ]}, NEW_VALUE, "$$REMOVE"  ] }
                }
            }
        }
    }
  ]
)

2) Aggregate and Update:

db.collection.aggregate( [
  {
      $match: { _id: 1 }  // { _id: 2 }
  },
  { 
      $addFields: { 
          readAt: { 
              $let: {
                  vars: { readAtExists: { $ifNull: [ "$readAt", null ] } },
                  in: { $cond: [ {$eq: [ "$$readAtExists", null ]}, NEW_VALUE, "$$REMOVE"  ] }
              }
          }
      }
  }
] ).forEach( doc => db.collection.replaceOne( { _id: doc._id }, doc ) )

NOTES: You can use the NOW aggregation variable with MongoDB v4.2 for setting the date value in an aggregation.

Toggle Mongo Document Date Property With Single Query

2 Answers2