MongoDB Update Pipeline Tricks

README.md

Starting with MongoDB 4.2, you can use aggregation pipelines to update documents. Which leads to some really cool stuff.

For example, prior to this you could easily add sub-documents to an array using $addtoSet, and you could remove documents from an array using $pull, but you couldn't do both in the same operation, you had to send two separate update commands if you needed to remove some and add some.

With 4.2, now you can, because you can format your update as a pipeline, with multiple $set and $unset stages, which makes those things possible. However, since this is so new I had a really hard time finding examples of many of the things I wanted to do, so I started to collect some here for my reference (and yours).

See also:

• https://docs.mongodb.com/manual/tutorial/update-documents-with-aggregation-pipeline/
• https://www.mongodb.com/blog/post/coming-in-mongodb-42-pipeline-powered-updates-and-more-expressive-queries

add-and-remove-multiple-array-entries.js

// Update Alice's student record to record class changes
db.students.updateOne( { _id : 'alice' }, [
  // Remove the classes she dropped
  {
    $set : {
      classes : { $setDifference : [ '$classes', [ 'Finance 101' ] ] },
    },
  },
  // Add the classes she's taking intead
  {
    $set : {
      classes  : { $concatArrays : [ '$classes', [
        'Engineering 104',
        'Underwater Basket Weaving',
      ] ] }
    }
  },
] );

add-and-remove-multiple-subdocuments-from-array.js

// Update the team roster
db.teams.updateOne( { _id : 'team-one' }, [
  // Add 3 new players to the team
  {
    $set : {
      players : {
        $concatArrays : [
          '$players', [
            { number : 12, name : 'Alice' },
            { number : 18, name : 'Bob' },
            { number : 39, name : 'Chuck' },
          ],
        ],
      }
    }
  },
  // Remove someone from team by just one property (filter the players
  // list to only return players that don't have number 42)
  {
    $set : {
      players : {
        $filter : {
          input : '$players',
          as    : 'player',
          cond  : { $ne : [ '$$player.number', 42 ] }
        }
      }
    }
  },
  // You can expand this to remove multiple records at once:
  {
    $set : {
      players : {
        $filter : {
          input : '$players',
          as    : 'player',
          cond  : { $and : [
            // Remove any players with the number 0 or a blank name
            { $ne : [ '$$player.number', 0 ] },
            { $ne : [ '$$player.name', '' ] },
          ] },
        }
      }
    }
  },
] );

incrementing-numbers.js

// Pipeline updates don't support the `$inc` operator, but you can emulate it:
db.foo.updateOne( { _id : 'FOO' }, [ { $set : {
  _version : {
    $cond : {
      if    : { $eq : [ { $type : '$_version' }, 'null' ] },
      then  : 1,
      else  : { $add : [ '$_version', 1  ] },
    },
  }
} } ] );

switch-names.js

/**
 * Switch from separate `firstName` and `lastName` fields to a single
 * `name` field and update the last_updated field at the same time
 */
db.students.updateMany( {}, [
  // Set name field by concatenating the firstName and lastName fields
  { $set : { name : { $concat : [ "$firstName", " ", "$lastName" ] } } },
  // Then remove the firstName/lastName fields
  { $unset : { firstName : 1, lastName : 1 } },
  // Set last_updated to the current date/time
  { $set : { last_updated : "$$NOW" } },
] );

update-student-grades.js

/**
 * Update student records and set their average test score and grade
 * computed from that average.
 */
// update every student record
db.students.updateMany( {}, [
  // Compute the average of all the test scores and set that as the `average` field
  { $set: { average : { $trunc: [ { $avg: "$tests" }, 0 ] } } },
  // Then set the `grade` field based on that average
  { $set: { grade: { $switch: {
    branches: [
      { case: { $gte: [ "$average", 90 ] }, then: "A" },
      { case: { $gte: [ "$average", 80 ] }, then: "B" },
      { case: { $gte: [ "$average", 70 ] }, then: "C" },
      { case: { $gte: [ "$average", 60 ] }, then: "D" }
    ],
    default: "F"
  } } } }
] );

very-precise-array-updates-with-reduce.js

// It's possible to use $reduce to handle the "insert a document
// immediately after (or before) a matching document" use case:
db.marching_order.updateOne( { _id : 'column-one' }, [
  { $set : { marchers : { $reduce : {
    input         : '$marchers',
    initialValue  : [],
    in            : {
      // iterate through the marchers list (an array of documents with
      // a name property), and apply the rules in this $switch to each
      // matching person that we find...
      $switch : {
        // Within the `$reduce`, the `$$this` variable refers to the
        // current element of the list, and the `$$value` variable is
        // the accumulator..  For JavaScript devs it's something like:
        // $marchers.reduce( ( $$value, $$this ) => {} );
        branches  : [
          {
            // When we find "Bob"...
            case : { $eq : [ "$$this.name", "Bob" ] },
            // we want to add "Alice" right before him
            then : { $concatArrays : [
              // Note that the accumulator is already an array
              // (because of the `initialValue : []` up at the
              // beginning)...
              "$$value",
              // But for other values we need to wrap them in an
              // array.
              [ { name : "Alice", age : 11 } ],
              [ "$$this" ],
            ] },
          },
          {
            // When we find "Chuck"...
            case : { $eq : [ "$$this.name", "Chuck" ] },
            // we're going to add 4 people behind him
            then : { $concatArrays : [ "$$value", [
              "$$this",
              { name : "Dave", age : 20 },
              { name : "Dave Too", age : 19 },
              { name : "Also Dave", age : 17 },
              { name : "Elwood", age : 43 },
            ] ] },
          },
          {
            // We're also going to remove everyone from the list who
            // is bad at marching
            case : { $or : [
              // Zeke is terrible at marching
              { $eq : [ "$$this.name", "Zeke" ] },
              // Everyone under 3 just gets in the way
              { $lt : [ "$$this.age", 3 ] },
              // Everyone over 100 ends up needing a wheelchair
              { $gt : [ "$$this.age", 100 ] },
            ] },
            // Returning the accumulator without adding "$$this" to it
            // effectively removes that item from the list.
            then : "$$value",
          },
        ],
        // The default if none of the branches matched is to just add
        // that item the accumulator.
        default : { $concatArrays : [ "$$value", [ "$$this" ] ] },
      },
    },
  } } } }
] );