Update Array Children Sorted Order

Question

I have a collection containing objects with the following structure

{
"dep_id": "some_id",
"departament": "dep name",
"employees": [{
   "name": "emp1",
   "age": 31
  },{
   "name": "emp2",
   "age": 35
  }]
}

I would like to sort and save the array of employees for the object with id "some_id", by employees.age, descending. The best outcome would be to do this atomically using mongodb's query language. Is this possible?

If not, how can I rearrange the subdocuments without affecting the parent's other data or the data of the subdocuments? In case I have to download the data from the database and save back the sorted array of children, what would happen if something else performs an update to one of the children or children are added or removed in the meantime?

In the end, the data should be persisted to the database like this:

{
"dep_id": "some_id",
"departament": "dep name",
"employees": [{
   "name": "emp2",
   "age": 35
  },{
   "name": "emp1",
   "age": 31
  }]
}

Answer 1

The best way to do this is to actually apply the $sort modifier as you add items to the array. As you say in your comment "My actual objects have a "rank" and 'created_at'", which means that you really should have asked that in your question instead of writing a "contrived" case ( don't know why people do that ).

So for "sorting" by multiple properties, the following reference would adjust like this:

db.collection.update(
  {  },
  { "$push": { "employees": { "$each": [], "$sort": { "rank": -1, "created_at": -1 } } } },
  { "multi": true }
)

But to update all the data you presently have "as is shown in the question", then you would sort on "age" with:

db.collection.update(
  {  },
  { "$push": { "employees": { "$each": [], "$sort": { "age": -1 } } } },
  { "multi": true }
)

Which oddly uses $push to actually "modify" an array? Yes it's true, since the $each modifier says we are not actually adding anything new yet the $sort modifier is actually going to apply to the array in place and "re-order" it.

Of course this would then explain how "new" updates to the array should be written in order to apply that $sort and ensure that the "largest age" is always "first" in the array:

db.collection.update(
  { "dep_id": "some_id" },
  { "$push": { 
    "employees": { 
      "$each": [{ "name": "emp": 3, "age": 32 }],
      "$sort": { "age": -1 } 
    }
  }}
)

So what happens here is as you add the new entry to the array on update, the $sort modifier is applied and re-positions the new element between the two existing ones since that is where it would sort to.

This is a common pattern with MongoDB and is typically used in combination with the $slice modifier in order to keep arrays at a "maximum" length as new items are added, yet retain "ordered" results. And quite often "ranking" is the exact usage.

So overall, you can actually "update" your existing data and re-order it with "one simple atomic statement". No looping or collection renaming required. Furthermore, you now have a simple atomic method to "update" the data and maintain that order as you add new array items, or remove them.

Answer 2

In order to get what you want you can use the following query:

db.collection.aggregate({
    $unwind: "$employees" // flatten employees array
}, {
    $sort: {
        "employees.name": -1 // sort all documents by employee name (descending)
    }
}, {
    $group: { // restore the previous structure
        _id: "$_id",
        "dep_id": {
            $first: "$dep_id"
        },
        "departament": {
            $first: "$departament"
        },
        "employees": {
            $push: "$employees"
        },
    }
}, {
    $out: "output" // write everything out to a separate collection
})

After this step you would want to drop your source table and rename the "output" collection to match your source table name.

This solution will, however, not deal with the concurrency issue. So you should remove write access from the collection first so nobody modifies it during the process and then restore it once you're done with the migration.

You could alternatively query all data first, then sort the employees array on the client side and then use either single update queries or - faster but more complicated - a bulk write operation with all the individual update calls in order to update the existing documents. Here, you could use the entire document that you've initially read as a filter for the update operation. So if an individual update does not modify any document you'd know straight away, that some other change must have modified the document you read before. Those cases you'd need to retry later (or straight away until the update does actually modify a document).