How to sort documents based on length of an Array field

Question

In my small ExpressJS app, I have a Question model which was defined like that

var mongoose = require('mongoose'),
    Schema   = mongoose.Schema;

/**
 * Question Schema
 */
var Question = new Schema({
  title: {
    type: String,
    default: '',
    trim: true,
    required: 'Title cannot be blank'
  },
  content: {
    type: String,
    default: '',
    trim: true
  },
  created: {
    type: Date,
    default: Date.now
  },
  updated: {
    type: Date,
    default: Date.now
  },
  author: {
    type: Schema.ObjectId,
    ref: 'User',
    require: true
  },
  answers : [{
    type: Schema.ObjectId,
    ref: 'Answer'
  }]
});

module.exports = mongoose.model('Question', Question);

And I want to get list popular questions based on the answer numbers. The query I used to perform my purpose

Question.find()
  .sort({'answers.length': -1})
  .limit(5)
  .exec(function(err, data) {
    if (err) return next(err);
    return res.status(200).send(data);
  });

But I don't get anything. Do you have any solutions?

Answer 1

What you seem to mean here is that you want to "sort" your results based on the "length" of the "answers" array, rather than a "property" called "length" as your syntax implies. For the record, that syntax would be impossible here as your model is "referenced", meaning the only data present within the array field in the documents of this collection is the ObjectId values of those referenced documents.

But you can do this using the .aggregate() method and the $size operator:

Question.aggregate(
    [
        { "$project": {
            "title": 1,
            "content": 1,
            "created": 1,
            "updated": 1,
            "author": 1,
            "answers": 1,
            "length": { "$size": "$answers" }
        }},
        { "$sort": { "length": -1 } },
        { "$limit": 5 }
    ],
    function(err,results) {
        // results in here
    }
)

An aggregation pipeline works in stages. First, there is a $project for the fields in the results, where you use $size to return the length of the specified array.

Now there is a field with the "length", you follow the stages with $sort and $limit which are applied as their own stages within an aggregation pipeline.

A better approach would be to alway maintain the length property of your "answers" array within the document. This makes it easy to sort and query on without other operations. Maintaining this is simple using the $inc operator as you $push or $pull items from the array:

Question.findByIdAndUpdate(id,
    {
        "$push": { "answers": answerId },
        "$inc": { "answerLength": 1 } 
    },
    function(err,doc) {

    }
)

Or the reverse when removing:

Question.findByIdAndUpdate(id,
    {
        "$pull": { "answers": answerId },
        "$inc": { "answerLength": -1 } 
    },
    function(err,doc) {

    }
)

Even if you are not using the atomic operators, then the same principles apply where you update the "length" as you go along. Then querying with a sort is simple:

Question.find().sort({ "answerLength": -1 }).limit(5).exec(function(err,result) {

});

As the property is already there in the document.

So either do it with .aggregate() with no changes to your data, or change your data to alway include the length as a property and your queries will be very fast.

Answer 2

You can also use :

db.question.find().sort({"answers":-1}).limit(5).pretty();