How can I upsert only if a document is older than the new document in MongoDB?

user_515902 0
Viewed 843

I have a Mongo collection looking like this:

{
  "id": _id_,
  "doc": _some data_,
  "timestamp": _timestamp_
}

I would like to insert a new entry if there is none with the same id, or update the existing entry if it exists, but only update it if the new document has a timestamp greater than the existing entry.

What is the best way of achieving this in MongoDB? Can I do this in a single query?

aggregation-framework mongodb-query mongodb
3 Answers

You can do it in one DB transaction instead of multiple transactions but only if you've MongoDB version >= 4.2. Cause MongoDB .update()'s from or after version 4.2 will accept an aggregation pipeline in update part of query, Which helps to achieve this in one database call.

Query :

/** In the below query aggregation operator `$cond` helps to decide which value has to be set to respective fields &
 * will insert a new doc if `id` is not found in collection */
db.getCollection('collectionName').updateOne({"id" : inputId},
[
{$set : {doc : {$cond : [{$gt : [inputDateInDateFormat, '$timestamp']}, inputValueForDocField, '$doc']}}},
{$set : {timestamp : {$cond : [{$gt : [inputDateInDateFormat, '$timestamp']}, inputDateInDateFormat , '$timestamp']}}}
], {upsert : true})

Note : Ideally this query should work, but if any case if you're using older version of shell or any older version of mongo client (robo3T) it might fail in that case try using .update() instead of .updateOne() - Just saying from my previous experience.

user_72376130

AFAIK you can't do this in one query.You have to separate queries e.g.,

db.collection.findOne() to find out record.

const res = db.collection.findOne({_id: '5ebbc1ffe41253f2f8b336cf'});

If the record is not present then use db.collection.insertOne().

if (!res) {
  db.collection.insertOne({
    _id: '5ebbc1ffe41253f2f8b336cf',
    doc: '_some_data_',
    timestamp: '_timestamp_'
  });
}

If document already exists then

else {
  db.collection.updateOne(
    {
      _id: '5ebbc1ffe41253f2f8b336cf',
      timestamp: {$lt: _timestamp_}
    },
    {
      $set: {_some_data_}
    }
  )
}
user_73367140 
new_entry = {
               "id": some_id,
               "doc": some_data,
               "timestamp": some_timestamp
            }


 collection.update_one(
                {
                 'id': {$eq: new_entry['id']}, 
                 'timestamp': {$lte: new_entry['timestamp']}
                },
                {'$set': new_entry},
                upsert=True
           )
user_104114920
Related