Skip to content

Upsert

Estimated time to read: 2 minutes

Everything in MQL that is used to locate a document in a collection can also be used to modify the document.

For example: db.location.updateOne( { query_to_locate_document> }, { <update> } )

Upsert

upsert is a hybrid of update and insert, it should only be used when it is needed! Its functional use is that of conditional updates.

Syntax

upsert is added as a third parameter to the update() method.

db.location.updateOne( { query_to_locate_document> }, { <update> }, { "upsert": true } )

Usage

By default, upset is set to false.

When set to true, the following flow occurs:

%%{init: {"flowchart" : { "curve" : "linear" } } }%%

flowchart

    query[Query]
    upsert{{upsert?}}
    match{{Documents match </br>query filter?}}
    matched[Update the matched document]
    noMatched[Insert a new document]

    query --> upsert
    upsert --> |false| matched
    upsert --> |true| match
    match --> |true| matched
    match --> |false| noMatched

If upsert didn't exist, a search would have to be preformed before inserting new documents in order to avoid duplicate records with inconsistencies between them OR not search and get documents with identical information.

Example

Assume that we are running an IoT application that gathers data from various sources and accumulates it in the database.

This Data is then processed to communicate the status of things and other summaries on the accumulated data.

Smart Home Data Modelling

Following the above example, a Smart Home contains many IoT devices. Including but not limited to sensors, interactables, objects, etc

The Data Model for this would look as follows:

  • A separate collection per sensor
  • A separate document for 48 sensor readings

Each document contains:

  • Sensor ID
  • Date
  • Readings from the sensor in an array
  • Total number of readings
  • Summary values

Updating the sensor document

Whilst update or insert could be used for this operation, update() with upsert:true is a better course of action.

Assuming that r returns:

sensor = 5,
value = 72,
data = Date('2022-09-19'),
time = "1531"

db.iot.updateOne(
 { 
  "sensor": r.sensor, 
  "date": r.date,
  "valcount": { "$lt": 48 } 
 },

 {
  "$push": {
    "readings": {
      "v": r.value,
      "t": r.time 
    } 
  },
    "$inc": {
    "valcount": 1, 
    "total": r.value } 
   },
 { "upsert": true })

Example Syntax Breakdown

The syntax in the example above does the following:

db.iot.updateOne(
 { 
  "sensor": r.sensor, // Query for the sensor of the reading to match the document
  "date": r.date, // Query for the date of the reading to match the document
  "valcount": { "$lt": 48 } // Ensures that there are no more than 48 readings in the `readings` array, rather than querying the array field each time
 },

 {
  "$push": { // Push adds values into the `readings` array field
    "readings": {
      "v": r.value, // Set value to the given sensor value
      "t": r.time  // Set time to the given sensor time
    } 
  },
    "$inc": { // inc can be used to increment the value of a field
    "valcount": 1, // Increment the value of the valcount field by 1
    "total": r.value } // Update the total field by incrementing by the sensor value
   },
 { "upsert": true }) // Explicity set upsert. If the valcount field is equal or greater to 48, the document will no longer match the query. The command will instead insert a new document into the collection rather than updating it.