Sorting by virtual field in mongoDB (mongoose)

Question

Let's say I have some Schema which has a virtual field like this

var schema = new mongoose.Schema(
{
    name: { type: String }
},
{
    toObject: { virtuals: true },
    toJSON: { virtuals: true }
});

schema.virtual("name_length").get(function(){
    return this.name.length;
});

In a query is it possible to sort the results by the virtual field? Something like

schema.find().sort("name_length").limit(5).exec(function(docs){ ... });

When I try this, the results are simple not sorted...

Answer 1

Virtuals defined in the Schema are not injected into the generated MongoDB queries. The functions defined are simply run for each document at the appropriate moments, once they have already been retrieved from the database.

In order to reach what you're trying to achieve, you'll also need to define the virtual field within the MongoDB query. For example, in the $project stage of an aggregation.

There are, however, a few things to keep in mind when sorting by virtual fields:

• projected documents are only available in memory, so it would come with a huge performance cost if we just add a field and have the entire documents of the search results in memory before sorting
• because of the above, indexes will not be used at all when sorting

Here's a general example on how to sort by virtual fields while keeping a relatively good performance:

Imagine you have a collection of teams and each team contains an array of players directly stored into the document. Now, the requirement asks for us to sort those teams by the ranking of the favoredPlayer where the favoredPlayer is basically a virtual property containing the most relevant player of the team under certain criteria (in this example we only want to consider offense and defense players). Also, the aforementioned criteria depend on the users' choices and can, therefore, not be persisted into the document.

To top it off, our "team" document is pretty large, so in order to mitigate the performance hit of sorting in-memory, we project only the fields we need for sorting and then restore the original document after limiting the results.

The query:

[
  // find all teams from germany
  { '$match': { country: 'de' } },
  // project only the sort-relevant fields
  // and add the virtual favoredPlayer field to each team
  { '$project': {
    rank: 1,
    'favoredPlayer': {
      '$arrayElemAt': [
        {
          // keep only players that match our criteria
          $filter: {
            input: '$players',
            as: 'p',
            cond: { $in: ['$$p.position', ['offense', 'defense']] },
          },
        },
        // take first of the filtered players since players are already sorted by relevance in our db
        0,
      ],
    },
  }},
  // sort teams by the ranking of the favoredPlayer
  { '$sort': { 'favoredPlayer.ranking': -1, rank: -1 } },
  { '$limit': 10 },
  // $lookup, $unwind, and $replaceRoot are in order to restore the original database document
  { '$lookup': { from: 'teams', localField: '_id', foreignField: '_id', as: 'subdoc' } },
  { '$unwind': { path: '$subdoc' } },
  { '$replaceRoot': { newRoot: '$subdoc' } },
];

For the example you gave above, the code could look something like the following:

var schema = new mongoose.Schema(
  { name: { type: String } },
  {
    toObject: { virtuals: true },
    toJSON: { virtuals: true },
  });

schema.virtual('name_length').get(function () {
  return this.name.length;
});

const MyModel = mongoose.model('Thing', schema);

MyModel
  .aggregate()
  .project({
    'name_length': {
      '$strLenCP': '$name',
    },
  })
  .sort({ 'name_length': -1 })
  .exec(function(err, docs) {
    console.log(docs);
  });