How to sort documents by array field length

Question

In my small ExpressJS application, I have a question model that was defined as

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 a list of popular questions based on answer numbers. The query that I used to fulfill my goal

 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 get nothing. Do you have any solutions?

Answer 1

What you mean here is that you want to "sort" your results based on the "length" of the "answers" array, rather than the "property" called length, as your syntax implies. For writing, this syntax will not be possible here, since your model is “referenced”, that is, the only data that is in the array field in the documents of this collection are the ObjectId values ​​of these referenced documents.

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

 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 } ) 

The aggregation conveyor works in stages. First, there is $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 a length, you follow the steps $sort and $limit , which are used as your own steps in the aggregation pipeline.

A better approach would always be to maintain the length property of your response array in the document. This simplifies sorting and querying without other operations. Maintaining this is simple with the $inc operator, since you are $push or $pull elements from an array:

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

Or the opposite when deleting:

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

Even if you do not use atomic operators, then the same principles apply when you update the "length" as you move. Then the sort request is simple:

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

Because the property is already present in the document.

Thus, either do this with .aggregate() without any changes to your data, or change your data to always include 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();